SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 154
Accepted Solution

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

[ Edited ]

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:

 

Table_1.PNG

 

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!  


Accepted Solutions
Solution
‎03-19-2018 01:01 PM
Valued Guide
Posts: 558

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

View solution in original post


All Replies
Respected Advisor
Posts: 2,802

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

PROC TRANSPOSE

--
Paige Miller
Regular Contributor
Posts: 154

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

Posted in reply to PaigeMiller

Thanks @PaigeMiller

Super User
Posts: 23,228

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. 

Regular Contributor
Posts: 154

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

Nice Hint. Thanks!

Solution
‎03-19-2018 01:01 PM
Valued Guide
Posts: 558

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 218 views
  • 0 likes
  • 4 in conversation