topic Proc transpose: Missing by Group Values in SAS Procedures

Proc transpose: Missing by Group Values

JaneNYC — Tue, 11 Dec 2012 15:42:12 GMT

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.

Re: Proc transpose: Missing by Group Values

AncaTilea — Tue, 11 Dec 2012 15:52:31 GMT

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;

Re: Proc transpose: Missing by Group Values

JaneNYC — Tue, 11 Dec 2012 16:29:32 GMT

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.

Re: Proc transpose: Missing by Group Values

AncaTilea — Tue, 11 Dec 2012 16:42:38 GMT

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;

Re: Proc transpose: Missing by Group Values

art297 — Tue, 11 Dec 2012 17:07:43 GMT

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.

Re: Proc transpose: Missing by Group Values

ArtC — Wed, 12 Dec 2012 00:18:04 GMT

Jane,

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