topic Re: How to transpose one column into several columns (decoded binary) in SAS Data Management

How to transpose one column into several columns (decoded binary)

Crubal — Mon, 19 Mar 2018 17:02:39 GMT

Hi,

I have a question on how to transpose one column into several columns that is binary (0 or 1).

My prior dataset (Table_1) looks like below:

For each combination of 'Location' & 'Number', I would like to change the column 'Id' into several columns, Table_2 looks like:

Location | Number | Id_4 | Id_5 | Id_8 | Id_57 | Id_70 | Id_75 | Id_109 | Id_121 | Id_122 | Id_124 | Id_250 | Id_254 | Id_255 | ...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

L1 | 1040 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |.......

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

L1 | 1041 | 1 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 |.......

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

.......

In Table_2, new column 'Id_4 = 1' means that a combination of Location and Number has Id = 4 in Table_1. And 'Id_255 = 0' means that the combination does not have Id = 255 in Table_1.

How could I do that in SAS?

Thanks!

Re: How to transpose one column into several columns (decoded binary)

PaigeMiller — Mon, 19 Mar 2018 15:00:00 GMT

PROC TRANSPOSE

Re: How to transpose one column into several columns (decoded binary)

Reeza — Mon, 19 Mar 2018 15:08:10 GMT

Add a one and then use PROC TRANSPOSE.

Include ID in the ID statement, and add the relevant PREFIX as desired.

You’ll still need to add 0’s yourself.

Re: How to transpose one column into several columns (decoded binary)

SuryaKiran — Mon, 19 Mar 2018 16:39:40 GMT

You can PROC TRANSPOSE and then use DATA STEP to convert as needed.

DATA HAVE;
infile datalines dlm=" ";
input Location $ Number Id;
dummy=1;
datalines;
L1 1040 4
L1 1040 5
L1 1040 8
L1 1040 57
L1 1041 4
L1 1041 8
L1 1041 57
;
RUN;
proc transpose data=have out=pre_want(drop=_name_) prefix=ID_;
ID ID;
by Location Number;
run;
data want;
set pre_want;
Array All_Num _numeric_;
do over All_Num;
if All_Num=. then All_Num=0;
end;
run;

Re: How to transpose one column into several columns (decoded binary)

Crubal — Mon, 19 Mar 2018 17:01:37 GMT

Nice Hint. Thanks!

Re: How to transpose one column into several columns (decoded binary)

Crubal — Mon, 19 Mar 2018 17:01:57 GMT

Thanks @PaigeMiller