Proc Transpose

[Aman4SAS]

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

[LinusH]

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?

[Aman4SAS]

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.

[RW9 · Accepted solution]

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;

[LinusH]

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;

[Aman4SAS]

Thanks , Great help.