BookmarkSubscribeRSS Feed
sahoositaram555
Pyrite | Level 9

Hi experts, i have a dataset like below

data a;
input subjid 1-6 type 8 status 10 eventdate 12-19 ;
cards;
150004 1 . 2015-08-19
150004 2 1
180016 2 .
180016 1 . 2015-04-03
180020 1 1 .
180020 2 . 2015-08-20
180041 1 1 .
180041 1 . 2015-02-09
190004 1 1 .
100033 1 . 2017-01-26
100033 2 1 .
160071 1 1 .
160071 2 . 2016-02-28
460088 1 1 .
;
run;

i would like to make the final dataset with single observation for each subjid and if status,eventdate not missing for a perticular subjid then do status="Y", finaldate=eventdate;

Though i have found a way such as by doing a transpose and then writing a if statement to do the procedure, but looking from experts POV if there is a way to carryforword values both uprow (eg:for subjid 180016 )and downrow(as required 150004 ) the values based on a retain and by last. statement to solve/ any other efficient programming way this then it would be helpful to hear from you,experts.

 

 

4 REPLIES 4
sahoositaram555
Pyrite | Level 9

Hi @Kurt_Bremser, Thanks for the pick.
data a;
infile datalines missover;
input subjid type status eventdate ;

format eventdate YYMMDD10.;
datalines;
150004 1 . 20181
150004 2 1
180016 2 .
180016 1 . 20320
180020 1 1 .
180020 2 . 20128
180041 1 1 .
180041 1 . 20473
190004 1 1 .
100033 1 . 20502
100033 2 1 .
160071 1 1 .
160071 2 . 20913
460088 1 1 20942
;
run;

Kurt_Bremser
Super User

See this:

data a;
infile datalines missover; 
input subjid type status eventdate ;
format eventdate YYMMDD10.;
datalines;
150004 1 . 20181
150004 2 1
180016 2 .
180016 1 . 20320
180020 1 1 .
180020 2 . 20128
180041 1 1 .
180041 1 . 20473
190004 1 1 .
100033 1 . 20502
100033 2 1 .
160071 1 1 .
160071 2 . 20913
460088 1 1 20942
;

data want;
set a (rename=(status=_status));
by subjid notsorted;
retain status finaldate;
format status $1. finaldate yymmddd10.;
if first.subjid
then do;
  status = "";
  finaldate = .;
end;
status = ifc(_status,'Y',status);
finaldate = coalesce(finaldate,eventdate);
if last.subjid;
keep subjid status finaldate;
run;
Tom
Super User Tom
Super User

If you only want one observation per SUBJID there is not need to look ahead (or back).  Just aggregate over the subjid.

SQL is very good at that.

Your rules are not that clear so if this result is not what you want then explain the rules more clearly and provide the output you want for this input.

data a;
  input subjid type status eventdate :yymmdd. ;
  format eventdate YYMMDD10.;
datalines;
100033 1 . 2016-02-18
100033 2 1 .
150004 1 . 2015-04-03
150004 2 1 .
160071 1 1 .
160071 2 . 2017-04-04
180016 2 . .
180016 1 . 2015-08-20
180020 1 1 .
180020 2 . 2015-02-09
180041 1 1 .
180041 1 . 2016-01-20
190004 1 1 .
460088 1 1 2017-05-03
;

proc sql ;
  select subjid,max(status) as status
       , max(eventdate) as finaldate format=yymmdd10.
  from a
  group by subjid
  ;
quit;
  subjid    status   finaldate
------------------------------
  100033         1  2016-02-18
  150004         1  2015-04-03
  160071         1  2017-04-04
  180016         .  2015-08-20
  180020         1  2015-02-09
  180041         1  2016-01-20
  190004         1           .
  460088         1  2017-05-03

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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