*Copyright @ www.mycsg.in;

What is this lesson about

This lesson collects a few practical programming patterns that appear frequently in clinical SAS work
The focus is not on one procedure but on reusable logic patterns, such as creating shell rows, identifying complete pre- and post-treatment records, and comparing current rows with previous rows
These examples are intentionally small so the programming idea is easy to isolate and understand

Create dummy rows for shells or layout templates

In reporting work we often need rows to exist even before real result values are joined in. The following examples show different ways to create those shell rows.

Using explicit output statements

This is the most direct method when only a few rows are needed
Each `output` writes one row with the manually assigned values

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 or Latino"; output;
run;

The dataset contains one observation for each shell row we coded explicitly

Using DATALINES

This is convenient when the shell values are easier to list, like a small text table
The delimiter separates the values for each variable

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 with a DO loop

This method is useful when coded values already map naturally to display text
The format stores the mapping, and the loop generates the possible coded values

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

SQL can also create small shell datasets when the values are known in advance
This style is useful when the surrounding workflow is already SQL-based

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 to generate patterned rows

Loops are especially useful when shell rows follow a repeated structure
This is common for visits, treatment groups, and parameter-level shells

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;

Inspect the three loop-based datasets and confirm how nested loops generate all combinations of the listed values

Check whether a subject has both pretreatment and post-treatment non-missing results

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;

The objective is to identify subject-parameter combinations that have at least one non-missing pretreatment result and at least one non-missing post-treatment result

Using PROC SQL with separate pre- and post-tables followed by an INNER JOIN

First create one dataset for non-missing pretreatment records and another for non-missing post-treatment records
Then join them on subject and parameter

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 with INTERSECT

This approach is shorter when the two sets have the same columns and only common rows are needed

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 with a subquery

The subquery method filters pretreatment rows to only those subject-parameter combinations that also exist post-treatment

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;

Select all records for subject-parameter combinations that satisfy the rule

Once the qualifying subject-parameter combinations are identified, we can join them back to the original dataset to keep all related rows

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 and MERGE

This approach uses standard DATA step-style processing instead of SQL
It is often useful when the surrounding program is already sort- and merge-based

proc sort data=adlb out=pre_trt02(keep=usubjid paramcd) nodupkey;
   by usubjid paramcd;
   where aval ne . and ady le 1;
run;
 
proc sort data=adlb out=post_trt02(keep=usubjid paramcd) nodupkey;
   by usubjid paramcd;
   where aval ne . and 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;
 
data all02;
   merge adlb(in=a) pre_n_post04(in=b);
   by usubjid paramcd;
   if a and b;
run;

Carry a previous row value forward for comparison with the current row

Check whether the current start date equals the previous end date

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 the LAG function

`lag` can bring the value from the previous execution of the function call
It should be used carefully because it is queue-based, not simply row-based
Reset logic is still needed at BY group boundaries

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 and prev_end ne sestdtc then flag="Y";
run;

Using RETAIN instead of LAG

The retained variable method is often easier to reason about in clinical programming
After checking the current row, the previous end date is updated for the next row

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;

Compare `se02` and `se03` and decide which logic is easier for you to maintain and explain

Key points to remember

Common logic patterns often reappear across many projects, so it is worth learning them as reusable templates
There are usually multiple ways to solve the same requirement in SAS, such as SQL versus sort and merge
Choose the approach that is clearest, safest, and easiest to validate in your programming context

*Copyright @ www.mycsg.in;

What is this lesson about

Create dummy rows for shells or layout templates

Using explicit output statements

SAS Log

Dataset View

Using DATALINES

SAS Log

Dataset View

Using PROC FORMAT with a DO loop

SAS Log

Dataset View

Using PROC SQL

SAS Log

Dataset View

Using DO loops to generate patterned rows

SAS Log

Dataset View

Check whether a subject has both pretreatment and post-treatment non-missing results

Create example data

SAS Log

Dataset View

Using PROC SQL with separate pre- and post-tables followed by an INNER JOIN

SAS Log

Using PROC SQL with INTERSECT

SAS Log

Using PROC SQL with a subquery

SAS Log

Dataset View

Select all records for subject-parameter combinations that satisfy the rule

SAS Log

Dataset View

Using PROC SORT NODUPKEY and MERGE

SAS Log

Dataset View

Carry a previous row value forward for comparison with the current row

Check whether the current start date equals the previous end date

Create sample data

SAS Log

Using the LAG function

SAS Log

Dataset View

Using RETAIN instead of LAG

SAS Log

Dataset View

Key points to remember