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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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?


 

View solution in original post

2 REPLIES 2
Reeza
Super User
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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 502 views
  • 3 likes
  • 2 in conversation