topic Re: Combine IDs and dates in a new variable and add a suffix in New SAS User

Combine IDs and dates in a new variable and add a suffix

NewUsrStat — Mon, 06 May 2024 10:18:35 GMT

Suppose to have the following:

data DB;
  input ID :$20. Discharge :date9.;
  format Discharge date9.;
cards;
0001 19JUN2017
0001 07SEP2020
0002 17MAR2016
0003 05MAY2016
0003 08FEB2017
0004 22MAR2017
0004 03MAY2017
0004 28MAR2021
;


data DB1;
  input ID :$20. Discharge :date9. Combined;
  format Discharge date9.;
cards;
0001 19JUN2017  0001_19JUN2017_1
0001 07SEP2020  0001_07SEP2020_2
0002 17MAR2016  0002_17MAR2016_1
0003 05MAY2016  0003_05MAY2016_1
0003 08FEB2017  0003_08FEB2017_2
0004 22MAR2017  0004_22MAR2017_1
0004 03MAY2017  0004_03MAY2017_2
0004 28MAR2021  0004_28MAR2021_3
;

Is there a way to combine IDs and dates in a new variable and add an incremental number (after sorting by ID and Discharge) as suffix?

Desired output: DB1

Thank you in advance

Re: Combine IDs and dates in a new variable and add a suffix

PaigeMiller — Mon, 06 May 2024 10:23:54 GMT

Thanks for providing working DATA step code!

data want;
    set db;
    combined=catx('_',id,put(discharge,date9.));
run;

Re: Combine IDs and dates in a new variable and add a suffix

ballardw — Mon, 06 May 2024 21:51:53 GMT

This is includes the "incremental number":

data want;
    set db;
    by id;
    if first.id then counter=1;
    else counter+1;
    combined=catx('_',id,put(discharge,date9.),counter);
   drop counter;
run;