Transpose and auto-populating

[stancemcgraw]

I have SAS desktop 9.4 I want to eventually have data want2 below, but wondered how I got from data have to data want 2. Do I need to pull down the corresponding accountnum so that each comorbidity has one, even if it's the same multi-row? 

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

[Reeza]

---

@stancemcgraw wrote:

Do I need to pull down the corresponding accountnum so that each comorbidity has one, even if it's the same multi-row? 

Yes you would need to do that. Look at RETAIN. 

For creating dummy variables automatically there are many options:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-dummy-variables-Categorical-Var...

Here's an illustration of GLMMOD and then some links to other options at the bottom. 

---

@stancemcgraw wrote:

I have SAS desktop 9.4 I want to eventually have data want2 below, but wondered how I got from data have to data want 2. Do I need to pull down the corresponding accountnum so that each comorbidity has one, even if it's the same multi-row? 

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

---

[data_null__]

Just using RETAIN will NOT automatically carry forward the ID variable.  The UPDATE trick shown below will do that. Then is just PROC TRANSPOSE.

data have;
   input Accountnum :$10. Comorbidities &$16.;
   cards;
240063848            Hypertension
       .             Dementia
       .             Mental health
240063799            Carotid
.                    Drug abuse
;;;;
   run;
proc print;
   run;
data havev / view=havev;
   set have;
   retain one 1;
   run;
data want1;
   update havev(obs=0) havev;
   by one;
   output; /*Important*/
   run;
proc print;
   run;
proc transpose data=want1 out=want2(drop=_:);
   by acc: notsorted;
   var one;
   id co:;
   run;
proc print;
   run;

[akash1088]

Check this.

Option missing=0;
Data want1(Drop=Accountnum Rename=(m=Accountnum));
infile datalines dsd missover dlm=' ';
input Accountnum:8.      Comorbidities$:25.;
Freq=1;
retain m;
if Accountnum ne . then m=Accountnum;
Datalines;
240063848 Hypertension
 Dementia
 Mental health
240063799 Carotid
 Drug abuse
;
run;



Proc Sort Data=want1;
by Accountnum;
Proc TRanspose data=want1 out=want2;
by Accountnum;
id Comorbidities;
var freq;
run;

[stancemcgraw]

Thanks Akash1088. I don't use the infile or datelines statement and it's only coding the first comorbidity and dropping them rest of the them....

I used :

Option missing=0;

Data want1(Drop=Accountnum Rename=(m=Accountnum));

set pentrmr1;

put Accountnum:8.Comorbidities$:25.;

Freq=1;

retain m;

if Accountnum ne . then m=Accountnum;

run;

Proc Sort Data=want1;

by Accountnum;run;

Proc TRanspose data=want1 out=want2;

by Accountnum;

id Comorbidities;

var freq;

run;