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

[Crubal]

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!  

[PaigeMiller]

PROC TRANSPOSE

[Crubal]

Thanks @PaigeMiller

[Reeza]

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. 

[Crubal]

Nice Hint. Thanks!

[SuryaKiran · Accepted solution]

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;