Help using Base SAS procedures

Proc transpose: Missing by Group Values

Reply
Occasional Contributor
Posts: 9

Proc transpose: Missing by Group Values

Hi-

I’m having trouble with getting the correct output using proc transpose.  In the data set below you’ll notice for the first two observations there are missing by group values ID_CODE and Office.

data have;

input ID_CODE Office $ Name $ Rate;

datalines;

. . G19 1

. . G20 0

848 PHX G21 0.5

848 PHX L1 0

848 PHX L3 .

848 PHX P5 1

1084 PHX P6 0.5

1084 PHX P7 1

1084 PHX P8 1

1084 PHX P10 0

1084 PHX S33 0.5

1084 PHX S35 0

1084 PHX WB15 .

1084 PHX WB22 .

1084 PHX WB25 1

;

My program.

proc transpose data=have  out=want (drop=_Smiley Happy;

var  Rate;

by ID_CODE Office;

id Name;run;

When I run the program above this is my output.

ID_CODEOfficeG19G20G21L1L3P5P6P7P8P10S33S35WB15WB22WB25
. ...............
848PHX..0.50.1.........
1084PHX......0.51100.50..1

I need to drop the first missing observation but keep the first two transposed vars G19 and G20.

ID_CODEOfficeG19G20G21L1L3P5P6P7P8P10S33S35WB15WB22WB25
848PHX..0.50.1.........
1084PHX......0.51100.50..1

Any feedback is appreciated. Thanks. 

Super Contributor
Posts: 543

Re: Proc transpose: Missing by Group Values

Hi,

Using your code, you could add one more option:

proc transpose data=have  out=want (drop=_: where = (id_code ne .));

var  Rate;

by ID_CODE Office;

id Name;

run;

Occasional Contributor
Posts: 9

Re: Proc transpose: Missing by Group Values

Hi there. Thanks for your feedback.  your modification did not work. I still get the same result. the vars G19 and G20 drop. I need to keep both of them in the data set even though when the data set is transposed their values turn to missing. 

Super Contributor
Posts: 543

Re: Proc transpose: Missing by Group Values

09:48 Tuesday, December 11, 2012

Obs  ID_CODE  Office  G19  G20  G21  L1  L3  P5   P6  P7  P8  P10  S33  S35  WB15  WB22  WB25

  1 848PHX..   0.5   0   .   1   ..   .   ......
  21084PHX....   .   .  0.5   1   1   0   0.5   0..1

This is what I get when I run the code below.

proc transpose data=have  out=want (drop=_: where = (id_code ne .));

var  Rate;

by ID_CODE Office;

id Name;

run;

You could also do an additional data step:

proc transpose data=have  out=want (drop=_: );

var  Rate;

by ID_CODE Office;

id Name;

run;

data really_want;

     set want(where = (id_code ne .));

run;

Valued Guide
Posts: 632

Re: Proc transpose: Missing by Group Values

Jane,

Did you make sure that the WHERE= was applied to the OUT= data set?

PROC Star
Posts: 7,363

Re: Proc transpose: Missing by Group Values

Jane: What version of SAS are you running and can you post the code you tried?  On 9.3, using Anca's code, I get the same results as Anca gets.

Ask a Question
Discussion stats
  • 5 replies
  • 195 views
  • 0 likes
  • 4 in conversation