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
@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:
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
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: