SAS Day 16: Proc SQL 1
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.
Usubjid=TF-001-001-001, VISIT=SCREENING, VSDY=-6, EPOCH=SCREENING
SAS Merge is perfect for 1 to 1 merge or many to 1 merge.
Such as, 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.
proc sql; create table dummy as select a.*, b.epoch from dummy_vs as a left join dummy_se 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;
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! 😇