Help using Base SAS procedures

Proc Transpose

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

Proc Transpose

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


Accepted Solutions
Solution
‎06-18-2014 04:58 AM
Super User
Super User
Posts: 7,942

Re: Proc Transpose

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;

View solution in original post


All Replies
Super User
Posts: 5,427

Re: Proc Transpose

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?

Data never sleeps
Super Contributor
Posts: 266

Re: Proc Transpose

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.

Solution
‎06-18-2014 04:58 AM
Super User
Super User
Posts: 7,942

Re: Proc Transpose

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;

Super User
Posts: 5,427

Re: Proc Transpose

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;

Data never sleeps
Super Contributor
Posts: 266

Re: Proc Transpose

Thanks , Great help.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 246 views
  • 3 likes
  • 3 in conversation