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.
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;
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.
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;
Jane,
Did you make sure that the WHERE= was applied to the OUT= data set?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.