topic Re: Conditional replacement of a variable values in New SAS User

Conditional replacement of a variable values

NewUsrStat — Sat, 23 Nov 2024 16:41:58 GMT

Hi guys,

suppose to have the following:

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Index; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 1
0001 21FEB2015 31DEC2015 0
0001 01MAR2018 30SEP2018 0
0001 01JAN2019 31DEC2019 0
0002 01JAN2015 31DEC2015 0
0002 01JAN2019 31OCT2019 0
0003 08FEB2014 10MAR2014 1
0003 16JUN2015 13JUL2015 0
0004 04MAY2016 10MAY2016 1
0004 13SEP2017 15NOV2017 1
0004 09DEC2018 31DEC2018 0
;

Is there a way to get the following?

data DB1;
  input ID :$20. Admission :date09. Discharge :date09. Index; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 0
0001 21FEB2015 31DEC2015 0
0001 01MAR2018 30SEP2018 0
0001 01JAN2019 31DEC2019 0
0002 01JAN2015 31DEC2015 0
0002 01JAN2019 31OCT2019 0
0003 08FEB2014 10MAR2014 0
0003 16JUN2015 13JUL2015 0
0004 04MAY2016 10MAY2016 0
0004 13SEP2017 15NOV2017 0
0004 09DEC2018 31DEC2018 0
;

In other words, for each patient, if there is at least one 0 in the column Index (doesn't matter the date) then Index = 1 should be converted to Index = 0.

Thank you in advance

Re: Conditional replacement of a variable values

Tom — Sat, 23 Nov 2024 17:41:27 GMT

So you want to set INDEX to the minimum value of INDEX for each ID?

In PROC SQL you could do that like this:

proc sql;
  create table db1 as
  select id,admission,discharge,min(index) as Index
  from db
  group by id
;
quit;

That works because PROC SQL will remerge the aggregate value back onto the detail observations.

Or you could calculate the minimum and then do the remerge.

proc summary data=db ;
  by id;
  var index ;
  output out=summary(keep=id index) min= ;
run;
data db1;
  merge db summary;
  by id;
run;

Re: Conditional replacement of a variable values

NewUsrStat — Sat, 23 Nov 2024 17:50:05 GMT

Thank you very much Tom for your help. Yes, basically I would like to set Index at minimum.