BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
112211
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

Patrick_0-1698215027541.png

 

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

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;

Patrick_0-1698215027541.png

 

Oligolas
Barite | Level 11

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 -

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2075 views
  • 0 likes
  • 4 in conversation