topic Re: Proc Transpose in SAS Procedures

Proc Transpose

Aman4SAS — Wed, 18 Jun 2014 05:53:04 GMT

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

Re: Proc Transpose

LinusH — Wed, 18 Jun 2014 07:39:29 GMT

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?

Re: Proc Transpose

Aman4SAS — Wed, 18 Jun 2014 08:47:39 GMT

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.

Re: Proc Transpose

RW9 — Wed, 18 Jun 2014 08:58:36 GMT

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;

Re: Proc Transpose

LinusH — Wed, 18 Jun 2014 09:04:41 GMT

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;

Re: Proc Transpose

Aman4SAS — Wed, 18 Jun 2014 09:13:22 GMT

Thanks , Great help.