Dear friends,
I have dataset like:
Data test;
input city$ period sales;
datalines;
a 1 4855
a 2 7584
a 3 7485
a 4 5656
b 1 3855
b 2 6584
b 3 4585
b 4 5356
c 1 2655
c 2 6384
c 3 3585
c 4 2556
;
run;
proc print; run;
proc sort; by city period; run;
proc transpose data = test out=test2(drop=_name_);
id city;
var sales;
by city period;
run;
proc print; run;
I am getting output like this:
1 a 1 4855 . .
2 a 2 7584 . .
3 a 3 7485 . .
4 a 4 5656 . .
5 b 1 . 3855 .
6 b 2 . 6584 .
7 b 3 . 4585 .
8 b 4 . 5356 .
9 c 1 . . 2655
10 c 2 . . 6384
11 c 3 . . 3585
12 c 4 . . 2556
is it possible to get desired output with the help of proc transpose? currently i need to do , extracting each city then merging all by period.
Kindly suggest.
Desired output n Period a b c
1 1 4855 3855 2655
2 2 7584 6584 6384
3 3 7485 4585 3585
4 4 5656 5356 2556
Your issue is that you are transposing by both city and period:
Sort by period,city. Then:
proc transpose data=have out=want;
by period;
var sales;
id city;
idlabel city;
run;
Don't follow you, lines 5-8 is for 'b', but in the end result you wish to keep City as a column?
Don't you want:
n Period a b c
1 1 4855 3855 2655
2 2 7584 6584 6384
3 3 7485 4585 3585
4 4 5656 5356 2556
This look like a report lay-out (and there are other more suitable tools for that), or what do wish to do with it?
My desired output is
n Period a b c
1 1 4855 3855 2655
2 2 7584 6584 6384
3 3 7485 4585 3585
4 4 5656 5356 2556
yes there is other methodology to do it. I m just looking that can i do that by proc transpose or not.
i have dataset where sales values for US and states are in same colume ,
after transposing i need to create gplot for 5 states with nation to do comparison analysis.
Your issue is that you are transposing by both city and period:
Sort by period,city. Then:
proc transpose data=have out=want;
by period;
var sales;
id city;
idlabel city;
run;
Don't know how you will go to state from city, but you can plot without transposing:
symbol interpol=spline;
proc gplot data=test;
plot sales*period=city ;
run;
quit;
Thanks , Great help.
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.