topic Re: Modification to proc transpose output in SAS Programming

Modification to proc transpose output

luch25 — Thu, 27 Aug 2020 15:40:49 GMT

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?

Re: Modification to proc transpose output

Reeza — Thu, 27 Aug 2020 15:43:35 GMT

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?

Re: Modification to proc transpose output

luch25 — Thu, 27 Aug 2020 15:53:30 GMT

thank you @Reeza!