*Copyright @ www.mycsg.in;

*Obtaining descriptive statistics; *Finding duplicates; *Removing duplicates; *Converting dates; *array transpose; *lag function; *retain statement; *dummy rows based on some values present in data; *Finding the maximum result-max func and retain, proc means, proc sql; *Checking the number of records with missing/non-missing information -missfmt based; */

Programming for the Task

Dummy rows creation -for the tfl layouts where all rows displayed in the shell have to be presented

Using explicit output statements

data explicit01;
   length label $100;
   group=1; order=1; label="Male"; output;
   group=1; order=2; label="Female"; output;
   group=2; order=1; label="Hispanic or Latino"; output;
   group=2; order=2; label="Not Hispanic of Latino"; output;
run;

Using datalines statement

data datalines01;
   infile datalines truncover dlm='|';
   input group order label $100.;
datalines;
1|1|Male
1|2|Female
2|1|Hispanic or Latino
2|2|Not Hispanic or Latino
;
run;

Using proc format and do loop

proc format;
   value createdummy
   101="Male"
   102="Female"
   201="Hispanic or Latino"
   202="Not Hispanic or Latino"
   other=" "
   ;
run;
 
data format01;
   length label $100;
   do i=100 to 400;
      label=put(i,createdummy.);
      if label ne "" then output;
   end;
run;

Using proc sql

proc sql;
   create table sqldummy01
      (group num, order num, label char(100));
   insert into sqldummy01
      values(1,1,'Male')
      values(1,2,'Female')
      values(2,1,'Hispanic or Latino')
      values(2,2,'Not Hispanic or Latino')
   ;
quit;

Using do loops

data loop01;
   do avisitn=1,2,4,6;
      do treatment=1 to 3;
         output;
      end;
   end;
run;
 
data loop02;
   do treatment="A","B";
      do order=1 to 3;
         output;
      end;
   end;
run;
 
data loop03;
   do visit="WEEK 1", "WEEK 2", "WEEK 3";
      do vstestcd="WEIGHT","HEIGHT", "BMI";
         output;
      end;
   end;
run;

Check if a subject has a non-missing result for a parameter pretreatment and post treatment

Create example data

data adlb;
   infile cards truncover;
   input usubjid paramcd$ ady aval;
cards;
1001 GLUC -4 123
1001 GLUC 1 120
1001 GLUC 8 132
1002 GLUC 2 154
1002 GLUC 8 152
1003 GLUC -5 180
1003 GLUC -1 184
1004 GLUC 1 101
1004 GLUC 8 .
;
run;

Using proc sql - approach 1 - making use of inner join

proc sql;
   create table pre_trt01 as
      select distinct usubjid,paramcd
      from adlb
      where ady le 1 and aval ne .;
 
   create table post_trt01 as
      select distinct usubjid,paramcd
      from adlb
      where ady gt 1 and aval ne .
      ;
 
   create table pre_n_post01 as
      select a.usubjid,a.paramcd
      from pre_trt01 as a
      inner join
      post_trt01 as b
      on a.usubjid=b.usubjid and a.paramcd=b.paramcd
      ;
quit;

Using proc sql - approach 2 - making use of intersect operator

proc sql;
   create table pre_n_post02 as
      select distinct usubjid,paramcd
      from adlb
      where ady le 1 and aval ne .
 
      intersect corr
 
      select distinct usubjid,paramcd
      from adlb
      where ady gt 1 and aval ne .
      ;
quit;

Using proc sql - approach 3 - making use of subquery

proc sql;
   create table pre_n_post03 as
      select distinct usubjid,paramcd
      from adlb
      where cats(usubjid,paramcd) in
         (select distinct cats(usubjid,paramcd)
         from adlb
         where ady gt 1 and aval ne .
         )
      and ady le 1 and aval ne .;
quit;

Selecting all records of subjects/paramter who has pre and post treatment result available

proc sql;
   create table all01 as
      select a.*
      from adlb as a
      inner join
      pre_n_post03 as b
      on a.usubjid=b.usubjid and a.paramcd=b.paramcd
   ;
quit;

Using proc sort nodupkey

proc sort data=adlb out=pre_trt02(keep=usubjid paramcd) nodupkey;
   by usubjid paramcd;
   where aval ne . and . lt ady le 1;
run;
 
proc sort data=adlb out=post_trt02(keep=usubjid paramcd) nodupkey;
   by usubjid paramcd;
   where aval ne . and . lt ady gt 1;
run;
 
data pre_n_post04;
   merge pre_trt02(in=a) post_trt02(in=b);
   by usubjid paramcd;
   if a and b;
run;
 
*also select all records of parameters of subjects who has both pre and post treatment records;
data all02;
   merge all01(in=a) pre_n_post04(in=b);
   by usubjid paramcd;
   if a and b;
run;

Carry forward a previous record value onto current record for comparison

Check if the start date on current record is same as the end date of previous record

Create sample data

data se;
   infile cards truncover;
   length sestdtc seendtc $10;
   input usubjid taetord etcd$ sestdtc$ seendtc$;
cards;
1001 1 SCR 2010-01-07 2010-01-10
1001 2 PBO 2010-01-10 2010-01-30
1001 3 FU  2010-02-01 2010-02-15
;
run;

Using lag function

proc sort data=se out=se01;
   by usubjid taetord;
run;
 
data se02;
   set se01;
   by usubjid taetord;
   length prev_end $10;
   if first.usubjid then call missing(prev_end);
   prev_end=lag(seendtc);
   if not first.usubjid then do;
      if prev_end ne sestdtc then flag="Y";
   end;
run;

Using retain statement and explicit output statements

data se03;
   set se01;
   by usubjid taetord;
   length prev_end $10;
   retain prev_end;
   if first.usubjid then call missing(prev_end);
   if not first.usubjid and sestdtc ne prev_end then flag="Y";
   output;
   prev_end=seendtc;
run;

Using datastep merge

data se91;
   set se01;
   by usubjid taetord;
   if first.usubjid then seq=1;
   else seq+1;
run;
 
data se_plus1;
   set se91;
   seq=seq+1;
   prev_end=seendtc;
   keep usubjid seq prev_end;
run;
 
data se92;
   merge se91(in=a) se_plus1;
   by usubjid seq;
   if a;
run;

Find the difference in result when compared to the result on previous record

proc sort data=adlb out=prevdiff01;
   by usubjid paramcd ady;
run;
 
data prevdiff02;
   set prevdiff01;
   by usubjid paramcd ady;
   if first.paramcd then call missing(prev_result);
   prev_aval=lag(aval);
   if nmiss(prev_aval,aval)=0 then diff=aval-prev_aval;
run;

Converting a variable's value to sentence case

Using upcase,lowcase functions and concatenation operator

data case01;
   orig_text="All Treatment-Emergent Adverse events";
   conv_text=upcase(substrn(orig_text,1,1))||trim(lowcase(substrn(orig_text,2)));
run;

Using propcase function

data case02;
   orig_text="All Treatment-Emergent Adverse events";
   conv_text=propcase(orig_text,'`');*choosing a character which does not appear in data as delmiter;
run;

Creating cartesian product

Create a row for each aedecod observed in AE dataset for each subject in study

Create input data

data adsl;
   infile cards truncover;
   input usubjid;
cards;
1001
1002
2001
;
run;
 
data adae;
   infile cards truncover dlm="|";
   length aedecod $100;
   input usubjid aedecod;
cards;
1001|Headache
1002|Diarrhea
2001|Gall stones
;
run;

Using proc sql

proc sql;
   create table allsubjevents01 as
      select a.*,b.aedecod
      from adsl as a,
      (select distinct aedecod
      from adae as b)
      ;
quit;

Using data step

 
proc sort data=adae out=aedecod(keep=aedecod) nodupkey;
   by aedecod;
run;
 
data allsubjevents02;
   set adsl;
   do i=1 to nobs;
      set aedecod nobs=nobs point=i;
      output;
   end;
run;

Get the latest transfusion date before each lab collection date

Create input data

data adtrans;
   infile cards truncover ;
   input usubjid transdt date9.;
   format transdt date9.;
cards;
1001 15Jan2010
1001 2Feb2010
1001 15Feb2010
1003 5Mar2009
run;
 
data adlb;
   infile cards truncover ;
   input usubjid paramcd$ adt date9. aval;
   format adt date9.;
   seq=_n_;
cards;
1001 HGB 1Feb2010 12.3
1001 HGB 2Feb2010 12.2
1001 HGB 10Feb2010 12.3
1002 HGB 1Jan2010 13.0
1003 HGB 15May2010 10.0
;
run;

Using proc sql

proc sql;
   create table adlb02 as
      select a.*,b.transdt
      from adlb as a
      left join
      adtrans as b
      on a.usubjid=b.usubjid and  (. lt transdt le adt)
      order by a.usubjid,paramcd,adt;
quit;
 
data adlb03;
   set adlb02;
   by usubjid paramcd adt seq;
   if last.seq;
run;

Using data step

data adlb04;
   set adlb;
   by usubjid;
   do i=1 to nobs;
      set adtrans(rename=(usubjid=tusubjid transdt=ttransdt)) nobs=nobs point=i;
      if usubjid=tusubjid and . lt ttransdt le adt then transdt=ttransdt;
      else transdt=.;
      output;
   end;
   format transdt date9.;
run;
 
proc sort data=adlb04;
   by usubjid paramcd adt transdt;
run;
 
data adlb05;
   set adlb04;
   by usubjid paramcd adt seq transdt;
   if last.seq;
   drop ttransdt tusubjid;
run;