Proc SQL

SAS Day 16: 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

image image

Background:

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.

[caption id="attachment_941" align="alignnone" width="750"] image

kaboompics / Pixabay[/caption]

Solution:

  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;

Output:

image

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! 😇