topic Re: Autopopulating with same ID for MR variable and then transposing. in SAS Programming

Autopopulating with same ID for MR variable and then transposing.

stancemcgraw — Mon, 24 Sep 2018 17:51:57 GMT

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

Re: Autopopulating with same ID for MR variable and then transposing.

AlokR — Mon, 24 Sep 2018 19:30:05 GMT

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;