Quartz | Level 8

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Meteorite | Level 14

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

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;``````
Thanks,
Suryakiran
5 REPLIES 5
Diamond | Level 26

PROC TRANSPOSE

--
Paige Miller
Quartz | Level 8

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

Thanks @PaigeMiller

Super User

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

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.

Quartz | Level 8

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

Nice Hint. Thanks!

Meteorite | Level 14

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

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;``````
Thanks,
Suryakiran
Discussion stats
• 5 replies
• 5568 views
• 0 likes
• 4 in conversation