data sdtm;
infile cards missover ;
input id RFICDTC :ddmmyy10. RFSTDTC :ddmmyy10. RFENDTC :ddmmyy10. FUMTC :ddmmyy10. lastdt:ddmmyy10.;
format RFICDTC RFSTDTC RFENDTC FUMTC ddmmyy10.;
CARDS;
101 08/02/1990 10/02/1991 12/05/1990 25/04/1990 25/10/1990
102 08/02/1990 10/02/1991 12/05/1990
103 08/02/1990 10/02/1991 12/05/1990 25/04/1990
104 08/02/1990 10/02/1991
105 08/02/1990 10/02/1991 12/05/2020 08/02/1990 20/10/1990
106 08/02/1990 10/02/1991 12/05/1990 25/04/1990
;
RUN;
I need output was below mentioned :
ID RFPENDTC
101 25/10/1990
102 12/05/1990
103 25/04/1990
104 10/02/1991
105 20/10/1990
106 25/04/1990
Telling us the selection rule would help.... By deducing what you shared it looks like you want to pick the last non-missing date based on the order of your date variables in the pdv. Below code is doing just that.
data sdtm;
infile cards missover;
input id RFICDTC :ddmmyy10. RFSTDTC :ddmmyy10. RFENDTC :ddmmyy10. FUMTC :ddmmyy10. lastdt:ddmmyy10.;
format RFICDTC RFSTDTC RFENDTC FUMTC lastdt ddmmyy10.;
CARDS;
101 08/02/1990 10/02/1991 12/05/1990 25/04/1990 25/10/1990
102 08/02/1990 10/02/1991 12/05/1990
103 08/02/1990 10/02/1991 12/05/1990 25/04/1990
104 08/02/1990 10/02/1991
105 08/02/1990 10/02/1991 12/05/2020 08/02/1990 20/10/1990
106 08/02/1990 10/02/1991 12/05/1990 25/04/1990
;
RUN;
data want;
set sdtm;
format rfpendtc ddmmyy10.;
rfpendtc=coalesce(lastdt,FUMTC,RFENDTC,RFSTDTC,RFICDTC);
run;
proc print data=want;
run;
What is the rule for building your target date?
Telling us the selection rule would help.... By deducing what you shared it looks like you want to pick the last non-missing date based on the order of your date variables in the pdv. Below code is doing just that.
data sdtm;
infile cards missover;
input id RFICDTC :ddmmyy10. RFSTDTC :ddmmyy10. RFENDTC :ddmmyy10. FUMTC :ddmmyy10. lastdt:ddmmyy10.;
format RFICDTC RFSTDTC RFENDTC FUMTC lastdt ddmmyy10.;
CARDS;
101 08/02/1990 10/02/1991 12/05/1990 25/04/1990 25/10/1990
102 08/02/1990 10/02/1991 12/05/1990
103 08/02/1990 10/02/1991 12/05/1990 25/04/1990
104 08/02/1990 10/02/1991
105 08/02/1990 10/02/1991 12/05/2020 08/02/1990 20/10/1990
106 08/02/1990 10/02/1991 12/05/1990 25/04/1990
;
RUN;
data want;
set sdtm;
format rfpendtc ddmmyy10.;
rfpendtc=coalesce(lastdt,FUMTC,RFENDTC,RFSTDTC,RFICDTC);
run;
proc print data=want;
run;
Hi,
looks like you got a mistake in the derivation you want.
SDTMIG says:
RFPENDTC=Date/time when subject ended participation or follow-up in a trial, as defined in the protocol, in ISO 8601
character format. Should correspond to the last known date of contact. Examples include completion date,
withdrawal date, last follow-up, date recorded for lost to follow up, and death date.
The date of the last column is not the latest date in your dataset and your lastdt seems to be erroneously derived.
In fact your whole dataset is erroneous since even your start date is after your end date
data want;
set sdtm;
wantRFPENTC=coalesce(lastdt,FUMTC,RFENDTC,RFSTDTC,RFICDTC);
CorrectRFPENDTC=max(lastdt,FUMTC,RFENDTC,RFSTDTC,RFICDTC);
format wantRFPENTC CorrectRFPENDTC ddmmyy10.;
keep id wantRFPENTC CorrectRFPENDTC ;
run;
- Cheers -
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.