Proc transpose: Missing by Group Values

[JaneNYC]

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=_:);

var  Rate;

by ID_CODE Office;

id Name;run;

When I run the program above this is my output.

ID_CODE	Office	G19	G20	G21	L1	L3	P5	P6	P7	P8	P10	S33	S35	WB15	WB22	WB25
.		.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
848	PHX	.	.	0.5	0	.	1	.	.	.	.	.	.	.	.	.
1084	PHX	.	.	.	.	.	.	0.5	1	1	0	0.5	0	.	.	1

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

ID_CODE	Office	G19	G20	G21	L1	L3	P5	P6	P7	P8	P10	S33	S35	WB15	WB22	WB25
848	PHX	.	.	0.5	0	.	1	.	.	.	.	.	.	.	.	.
1084	PHX	.	.	.	.	.	.	0.5	1	1	0	0.5	0	.	.	1

Any feedback is appreciated. Thanks. 

[AncaTilea]

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;

[JaneNYC]

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. 

[AncaTilea]

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	848	PHX	.	.   0.5   0   .   1   .	.   .   .	.	.	.	.	.
2	1084	PHX	.	.	.	.   .   .  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;

[ArtC]

Jane,

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

[art297]

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.