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-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.

Register now!

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
  • 626 views
  • 3 likes
  • 2 in conversation