topic Collapse occurrencies (indexes) present in multiple rows into one single row in New SAS User

Collapse occurrencies (indexes) present in multiple rows into one single row

NewUsrStat — Thu, 13 Jun 2024 17:24:14 GMT

Hi guys,

suppose to have the following:

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Morbidity1  Morbidity2  Morbidity3  Morbidity4;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017   1    0     1    0
0001  13JAN2017 25JAN2017   1    0     1    0
0001  22FEB2017 03MAR2017   0    1     0    0
0001  30JAN2019 04MAR2019   1    0     0    0
0002  01DEC2018 14DEC2018   1    0     1    0
0002  25DEC2018 02JAN2019   0    0     1    0
0002  25NOV2020 03DEC2020   1    1     1    1
0003  09JAN2016 25JAN2016   0    0     1    0
0003  29JAN2018 12FEB2018   0    0     1    1
...;

Is there a way to collapse all occurencies ("1" or "0") for the same ID?

Let say the desired output should be:


data DB1;
  input ID :$20.  Morbidity1  Morbidity2  Morbidity3  Morbidity4;
cards;
0001   1    1     1    0
0002   1    1     1    0
0003   0    0     1    1
...;

In other words regardless the admission-discharge specificity, the occurrencies should be collapsed into one row for each ID. Values in DB for Morbidity* variables are "1" or "0". No other values are present.

Thank you in advance

Re: Collapse occurrencies (indexes) present in multiple rows into one single row

mkeintz — Thu, 13 Jun 2024 17:38:07 GMT

It looks like you want the maximum value for each of the morbidity variables within a single ID. This is what PROC SUMMARY is for:

proc summary data=db ;
  by id;
  var morbidity1-morbidity4;
  output out=want (drop=_type_ _freq_)  max=;
run;

The above assume DB is sorted by ID. If not, you could:


proc summary data=db nway;
  class id;
  var morbidity1-morbidity4;
  output out=want (drop=_type_ _freq_)  max=;
run;

Re: Collapse occurrencies (indexes) present in multiple rows into one single row

ballardw — Thu, 13 Jun 2024 17:42:13 GMT

One way:

proc summary data=work.db nway;
   class id;
   var Morbidity: ;
   output out=work.db1 (drop=_:) max=;
run;

NWAY means only the combination of all CLASS variables is output. Otherwise there would be a row for an all dataset summary. The Drop in the output removes two variables _type_, indicating combination of CLASS variables and _freq_ number of observations used .

If you haven't seen the : list creator it mean use all variables whose names start with the characters before the colon.