I've written code using proc transpose (below), but it needs a modification that I can't quite figure out.
data a;
input id location $;
cards;
1 A
1 B
1 C
2 A
3 A
3 B
;
proc sql;
create table want1 as
select distinct id, location from a;
run;
proc transpose data = want1 out=want1a(drop =_name_) prefix = location_;
by id;
id location;
var location;
run;
The resulting output gives me the character value for the location (A, B, C) for each location_X variable. In other words, I get:
id location_A location_B location_C
1 A B C
2 A
3 A B
The information is correct, but instead of a character value, I wanted a binary indicator 1/0. So essentially, I'm looking for:
id location_A location_B location_C
1 1 1 0
2 1 0 0
3 1 1 0
Is there a way to modify my proc transpose code to get this? Or another way to do this?
data a;
input id location $;
cards;
1 A
1 B
1 C
2 A
3 A
3 B
;
proc sql;
create table want1 as
select distinct id, location, 1 as indicator from a;
run;
proc transpose data = want1 out=want1a(drop =_name_) prefix = location_;
by id;
id location;
var indicator;
run;
Add an indicator variable. You'll have to do a second step to add in the 0, but another way could be to look at a dummy variable type calculation instead.
@luch25 wrote:
I've written code using proc transpose (below), but it needs a modification that I can't quite figure out.
data a; input id location $; cards; 1 A 1 B 1 C 2 A 3 A 3 B ; proc sql; create table want1 as select distinct id, location from a; run; proc transpose data = want1 out=want1a(drop =_name_) prefix = location_; by id; id location; var location; run;
The resulting output gives me the character value for the location (A, B, C) for each location_X variable. In other words, I get:
id location_A location_B location_C
1 A B C
2 A
3 A B
The information is correct, but instead of a character value, I wanted a binary indicator 1/0. So essentially, I'm looking for:
id location_A location_B location_C
1 1 1 0
2 1 0 0
3 1 1 0
Is there a way to modify my proc transpose code to get this? Or another way to do this?
data a;
input id location $;
cards;
1 A
1 B
1 C
2 A
3 A
3 B
;
proc sql;
create table want1 as
select distinct id, location, 1 as indicator from a;
run;
proc transpose data = want1 out=want1a(drop =_name_) prefix = location_;
by id;
id location;
var indicator;
run;
Add an indicator variable. You'll have to do a second step to add in the 0, but another way could be to look at a dummy variable type calculation instead.
@luch25 wrote:
I've written code using proc transpose (below), but it needs a modification that I can't quite figure out.
data a; input id location $; cards; 1 A 1 B 1 C 2 A 3 A 3 B ; proc sql; create table want1 as select distinct id, location from a; run; proc transpose data = want1 out=want1a(drop =_name_) prefix = location_; by id; id location; var location; run;
The resulting output gives me the character value for the location (A, B, C) for each location_X variable. In other words, I get:
id location_A location_B location_C
1 A B C
2 A
3 A B
The information is correct, but instead of a character value, I wanted a binary indicator 1/0. So essentially, I'm looking for:
id location_A location_B location_C
1 1 1 0
2 1 0 0
3 1 1 0
Is there a way to modify my proc transpose code to get this? Or another way to do this?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.