topic Re: Convert Cell value to Column Variables (0,1 dummy) in SAS Enterprise Guide

Convert Cell value to Column Variables (0,1 dummy)

JChan10 — Tue, 02 Jun 2009 00:19:01 GMT

Hi,

Instead of keep using the following programming;
if diag_flag = 'A04' then Diagflag_A04_Dummy = 1;
else Diagflag_A04_Dummy = 0;
if diag_flag = 'A41' then Diagflag_A41_Dummy = 1;
else Diagflag_A41_Dummy = 0;
if diag_flag = 'C16' then Diagflag_C16_Dummy = 1;
else Diagflag_C16_Dummy = 0;.....

How can I convert my data in column (Diagflag) to multiple Dummy variables,
Data currently looking like
Diagflag
D12
C13
Q15
D12
A14

Want outcome with cell value become column heading and 0,1 as values
D12 C13 Q15 A14.....
1 0 0 0
0 1 0 0
0 0 1 0
1 0 0 0
0 0 0 1

Re: Convert Cell value to Column Variables (0,1 dummy)

sbb — Tue, 02 Jun 2009 18:02:58 GMT

SAS PROC TRANSPOSE can accomodate your requirement.

Scott Barry
SBBWorks, Inc.

Re: Convert Cell value to Column Variables (0,1 dummy)

JChan10 — Tue, 02 Jun 2009 21:19:21 GMT

I think the Transpose will put back the actual row observations back to the column but not the 0 and 1. I just want 0 and 1.

For example, under diagflag column, first obsevation is D12, then I would like to have a new column create named as D12 and the value in the first row under column D12 is 1.

Re: Convert Cell value to Column Variables (0,1 dummy)

sbb — Tue, 02 Jun 2009 21:54:11 GMT

Yes, you will need to carry a numeric variable to be used as an indicator, and use this variable by name in the PROC TRANSPOSE for the VAR statement along with the ID statement as well. Also code a PREFIX= to get the transposed columns having a defined prefix.

The key here is that you need to take the _N_ SAS-reserved variable (observation number) and assign it to a SAS numeric variable, say called OBSNUM. And this variable must be called-out in your ID statement so that you get one observation output for each transposed observation -- this approach maintains your vertical perspective.

What you will have generated is a new file, transposed with either a 1 or 0, depending on what was present in the input file.

Then, you will need to have a DATA step to loop through the transposed variables and if you find a missing value, change it to a zero -- that's better than setting MISSING='0' in my experience.

You'll find SAS-hosted product documentation and also supplemental technical and conference papers on various topics, like this one. I have provided a link at the bottom for one example on PROC TRANSPOSE.

Scott Barry
SBBWorks, Inc.

Learn the Basics of Proc Transpose
Douglas Zirbel, Wells Fargo and Co., St. Louis, Missouri
http://support.sas.com/resources/papers/proceedings09/060-2009.pdf