topic Re: Transpose and auto-populating in SAS Programming

Transpose and auto-populating

stancemcgraw — Mon, 24 Sep 2018 17:56:33 GMT

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

Re: Transpose and auto-populating

Reeza — Mon, 24 Sep 2018 18:11:27 GMT

@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-Variables/ta-p/308484

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

Re: Transpose and auto-populating

data_null__ — Mon, 24 Sep 2018 18:34:53 GMT

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;

Re: Transpose and auto-populating

akash1088 — Mon, 24 Sep 2018 18:37:50 GMT

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;

Re: Transpose and auto-populating

stancemcgraw — Mon, 24 Sep 2018 19:23:29 GMT

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;