Merging, SQL, and other SAS features

***************************************************************************
SAS program that uses the following functions and statements:
*******first.group, last.group
*******retain statement, output statement
*******proc SQL
*******merge statement
***************************************************************************;

***This program adjusts the concentration of one group of data on day 2 of testing because of the long halflife of the analyte that causes spill-over concentration.;

***Import concentrations file and parameters file;
libname Adjust 'C:\Adjustments';

proc import datafile='C:\Concentrations_file' dbms=xls out=Adjust.Conc replace;
run;

proc import datafile='C:\Parameters_file' dbms=xls out=Adjust.Param replace;
run;

***Set up dataset with only Day 2 and Group A;
data Conc;

format Group Subject Day Time Concentration;
set Adjust.Conc;
if Group='A';
if Day =2;

proc sort data=Conc;

by subject time;

run;



***Get the first concentration of each subject;
data firstconc;

set conc;
by subject;
if first.subject;
if Concentration=. then firstconc=0;
else firstconc=Concentration;
keep subject firstconc;

proc sort data=firstconc;

by subject;

run;

***Import parameter dataset to get halflife;
data param;

set adjust.param(rename=(HL_LAMBDA_Z__HR_=halflife));
if group='A';
if Day=1;
keep subject halflife;

proc sort data=param;

by subject;

run;

***Option 1 for finding Maximum concentration for each subject (SQL): Must group by subject and output only the concentrations that match the maximum concentration ("having" command);

proc sql;

create table cmax as
select subject, max(Concentration) as cmax, Concentration as tempconc, time as tmax
from conc
group by subject
having calculated cmax=tempconc

;
quit;

proc sort data=cmax;

by subject;

run;

***Option 2 for finding Cmax (retain statement): Brings down the previous concentration and compares it with current concentration so the last observation of each subject will have the largest concentration of the group (must use "by" statment;

proc sort data=conc;

by subject time;

run;

data lastconcset;

set conc;
by subject;
if first.subject then lastconc=0;
if lastconc>concentration then lastconc=lastconc;
else lastconc=concentration;
retain lastconc;
output;

run;

data cmax2;

set lastconcset;
by subject;
if last.subject;
cmax=lastconc;
keep subject cmax;
*To get tmax, you would have to merge this back in with full concentration dataset and do where cmax=concentration. Or use Cmax instead of Tmax in the merge below;

run;



***Merge together all datasets, adjust concentration according to equation and perform Below Limit of Quantitation rule;

data mergedparam_conc;

format Group $1. Subject Day Time Concentration k Adjustedconc Concentration cmax tmax best12.;
merge firstconc param conc cmax ;
by subject;

*adjust concentration based on elimination kinetics;
k=log(2)/halflife; *elimination constant k;

*From plasma conc equation Ct=Co*e^(-kt);Adjustedconc=Concentration-(firstconc*exp(-k*time));


if Adjustedconc<0 adjustedconc="0;" style="color: rgb(0, 153, 0);">

*BQL rule;
if CharConc='BQL(<2.0)'> then do;

if time<=tmax then adjustedconc=0; else adjustedconc=.;

end;
drop tempconc;

run;