BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Crubal
Quartz | Level 8

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!  

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

5 REPLIES 5
PaigeMiller
Diamond | Level 26

PROC TRANSPOSE

--
Paige Miller
Reeza
Super User

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. 

SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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