topic carry forword values both up and down rows in SAS Programming

carry forword values both up and down rows

sahoositaram555 — Fri, 24 Apr 2020 09:00:10 GMT

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.

Re: carry forword values both up and down rows

Kurt_Bremser — Fri, 24 Apr 2020 09:43:13 GMT

Please test your data step and correct it.

Re: carry forword values both up and down rows

sahoositaram555 — Fri, 24 Apr 2020 10:31:05 GMT

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;

Re: carry forword values both up and down rows

Kurt_Bremser — Fri, 24 Apr 2020 11:04:33 GMT

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;

Re: carry forword values both up and down rows

Tom — Fri, 24 Apr 2020 14:21:10 GMT

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