## Proc Transpose

Solved
Super Contributor
Posts: 271

# 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
Posts: 9,599

## 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;

All Replies
Super User
Posts: 5,884

## 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: 271

## 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
Posts: 9,599

## 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,884

## 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: 271

## Re: Proc Transpose

Thanks , Great help.

🔒 This topic is solved and locked.