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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.