topic need help re-arranging the data in New SAS User

need help re-arranging the data

ubshams — Tue, 19 Mar 2019 20:08:46 GMT

My data is currently in this format in SAS:

Current Output:

Month Variable Value

200501 variable1 0.01

200502 variable1 0.05

....

...

201812 variable1 0.05

200501 variable2 -0.05

200502 variable2 1.0

...

201812 variable2 1.5

How do I change it so it's in this format?

Desired Output

Month Variable1 Variable2

200501 0.01 -0.05

200502 0.05 1.0

...

201812 0.05 1.5

I thought of using the transpose procedure, but that will likely not give me what I want.

Thanks.

Re: need help re-arranging the data

novinosrin — Tue, 19 Mar 2019 20:13:54 GMT

data have;
input (Month      Variable ) ($10. :)   Value;
cards;
200501    variable1    0.01
200502    variable1    0.05
201812   variable1     0.05
200501    variable2    -0.05
200502    variable2    1.0
201812   variable2     1.5
;

proc sort data=have;
by month;
run;
proc transpose data=have out=want(drop=_name_);
by month;
var value;
id variable;
run;

Re: need help re-arranging the data

PaigeMiller — Tue, 19 Mar 2019 20:16:15 GMT

/* UNTESTED CODE */

proc sort data=have;
	by month variable;
run;
proc transpose data=have out=want(drop=_name_);
   by month;
   id variable;
run;

Re: need help re-arranging the data

novinosrin — Tue, 19 Mar 2019 20:20:44 GMT

Good catch to spot the missing secondary sort to get the order right

by month variable;

🙂 That's attention to detail indeed