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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.