BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aman4SAS
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
Aman4SAS
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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
Aman4SAS
Obsidian | Level 7

Thanks , Great help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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