SAS Proc SQL 1: Case When

Problem:
Suppose we need to merge the SDTM.VS (Vital Sign) dataset with SDTM.SE (Subject Element) for Epoch Infomation. We will assign the EPOCH to VS if the VSDY is between SESTDY and SEENDY.

Example:
Usubjid=TF-001–001–001, VISIT=SCREENING, VSDY=-6, EPOCH=SCREENING

Background:

SAS Merge is perfect for 1 to 1 merge or many to 1 merge.
Example,
1 record in dataset A merges with 1 record in dataset B.
1 record in dataset A merges with many records to dataset B, or vice versa.

Proc SQL is used/ Must be used for Many to Many merges.

kaboompics / Pixabay[/caption]

Solution:

proc sql; 
create table vsqc2 as select
 a.*, b.epoch from vsqc as a left join se1 
 as b on case when a.usubjid = b.usubjid 
and epoch="SCREENING" 
and b.sestdy <= a.vsdy < b.seendy then "screening" 
when a.usubjid = b.usubjid and epoch="TREATMENT" 
and b.sestdy <= a.vsdy < b.seendy 
then "treat" when a.usubjid = b.usubjid 
and epoch="FOLLOW-UP" and b.sestdy <= a.vsdy <= b.seendy then "follow" end ; 
quit;

OUTPUT:

Note: We need to use Proc Sort Nodupkey to after Proc SQL to get the unique records.

Thanks very much to Cindy to remind me SE means Subject Elements!

Happy SAS Coding!

Originally published at fangya18.com on November 7, 2018.