Autopopulating with same ID for MR variable and then transposing.

[stancemcgraw]

I have SAS desktop 9.4 and want to transpose my multi-row variable Comorbidity long ways.  Do I need to auto populate the ID to each of comorbidity (Data Want 1) or can I skip that step and transpose from Data Have to Data Want 2

Data have:

Accountnum      Comorbidities

240063848            Hypertension

           .                  Dementia

           .                   Mental health

240063799           Carotid

        .                     Drug abuse

Data want1:

Accountnum      Comorbidities

240063848            Hypertension

 240063848             Dementia

  240063848             Mental health

  240063799               Carotid

    240063799              Drug abuse

Eventually: data want2:

Accountnum       Hypertension      Dementia        Mental Health      Carotid      Drug Abuse

240063848                  1                     1                         1                       0                0

 240063799                 0                     0                         0                       1                1

[AlokR]

Yes as per my knowledge you will have to generate values for missing account numbers.
Ff you don't populate id for each comorbidity then SAS will group all missing values as one id.
Following code should work for you:

proc format;
 value zero . = '0';
run;

data Have(drop=PrevAccountnum);
 set Have;
 format count zero.;
 retain PrevAccountnum;

 if missing(Accountnum) then
 Accountnum=PrevAccountnum;
 else PrevAccountnum=Accountnum;
 
 Count=1;
run;

proc sort data=Have;
 by Accountnum Comorbidities;
run;

proc transpose data=have out=want(drop=_name_);
 id Comorbidities;
 by Accountnum;
 var count;
run;