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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 5410 views
  • 0 likes
  • 4 in conversation