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!
作者：乌然娅措
链接：https://www.jianshu.com/p/1b7e362ee275