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!
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;
PROC TRANSPOSE
Thanks @PaigeMiller
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.
Nice Hint. Thanks!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.