topic Re: multi colums sort and imput the results into new single column in SAS Procedures

multi colums sort and imput the results into new single column

ssas — Thu, 02 Apr 2009 15:32:47 GMT

Hi All,
i strucked at this point can any one help me.
I have various price coulmns and let use say their respective number of tickets sold

PRICE SPR09 PRICE AUT08 PRICE SUM08 PRICE SPR08 PRICE AUT07 PRICE SUM07 PRICE SPR07
0.00 727 0.00 2,768 0.00 838 0.00 652 0.00 4,581 0.00 1,184 0.00 344
1.90 1 1.50 183 2.00 65 2.50 615 2.00 11 1.00 526 2.50 691
3.00 1,480 1.75 9 2.50 122 2.75 1,092 2.75 371 2.00 7 3.00 432
3.50 2,198 2.50 112 2.75 174 3.50 597 3.00 156 2.50 690 3.25 334
3.75 218 3.00 740 3.00 60 3.70 370 3.50 328 2.70 4 4.00 340
3.80 235 3.50 867 3.50 103 4.00 397 3.75 32 3.00 96 4.25 45
4.00 66 3.75 166 3.70 250 4.50 356 4.00 138 3.25 273 4.50 5
4.50 133 3.80 293 4.00 197 5.00 1,719 4.50 4 3.50 367 5.00 143
5.00 574 4.00 795 4.50 352 5.25 11 5.00 1,388 3.60 6 5.25 25
5.50 100 4.50 1 4.75 2 6.00 2,396 5.25 1 3.75 2 6.00 1,150
6.00 381 5.00 800 5.00 814 6.50 31 6.00 357 4.00 629 6.25 9
6.50 5 5.25 80 5.25 1 7.00 1,327 7.00 924 4.50 200 6.50 112
7.00 641 5.50 7 6.00 257 7.50 1,553 7.50 555 5.00 818 7.00 1,721
7.50 523 6.00 210 6.50 220 8.00 1,492 8.00 1,618 5.40 9 7.50 434
8.00 915 6.30 2 7.00 176 8.25 1 8.25 6 5.50 80 8.00 1,027
8.25 1 6.40 9 7.50 5 8.50 1 8.50 1,591 5.85 4 8.50 1,943
8.50 18 6.50 17 7.75 1 8.75 5 8.75 1 6.00 908 9.00 566
8.75 13 6.65 1 7.90 1 9.00 3,102 9.00 2,150 6.25 1 9.50 350
9.00 2,334 6.75 1 8.00 1,611 9.50 780 9.25 357 6.38 3 10.00 544

i would like to concat all the prices into one coulm with thrier respective number of tickets

i would like to reorganize my data in the following form

price spr09 aut08 sum08 spr08 .........

0.00 120 253 354 236
1.00 256 542
1.25 85 356 236

i willbe very grateful if anyone could help me

Thanks,
sam Message was edited by: sams1

Re: multi colums sort and imput the results into new single column

deleted_user — Thu, 02 Apr 2009 15:55:22 GMT

Well firstly SAS won't let you have variables with the same name as another variable so this data could not exist in SAS.

I would suggest you are just going to have to assign the price values to a common vairable and output a record for each price.

Your request is a bit confusing.

Re: multi colums sort and imput the results into new single column

LAP — Thu, 02 Apr 2009 17:22:19 GMT

Assuming that your data is exactly as you show it AND I am understanding what you want. The easiest way I can see requires several steps.

First input the data. This will create a data set with price season and sales. You can then use a second data step to transpose back to the way you want it.

Data in_sales;
input price sales @@;
y=9; s=3; season = 'SPR09';
output;
do y = 8 to 7 by -1;
do s = 1 to 3;
input price sales : comma8. @@;
select (s);
when (1) season = 'AUT' || put(y,z2.);
when (2) season = 'SUM' || put(y,z2.);
when (3) season = 'SPR' || put(y,z2.);
otherwise;
end;
output;
end;
end;
input;
*PRICE SPR09 PRICE AUT08 PRICE SUM08 PRICE SPR08 PRICE AUT07 PRICE SUM07 PRICE SPR07;
cards;
0.00 727 0.00 2,768 0.00 838 0.00 652 0.00 4,581 0.00 1,184 0.00 344
1.90 1 1.50 183 2.00 65 2.50 615 2.00 11 1.00 526 2.50 691
3.00 1,480 1.75 9 2.50 122 2.75 1,092 2.75 371 2.00 7 3.00 432
3.50 2,198 2.50 112 2.75 174 3.50 597 3.00 156 2.50 690 3.25 334
3.75 218 3.00 740 3.00 60 3.70 370 3.50 328 2.70 4 4.00 340
3.80 235 3.50 867 3.50 103 4.00 397 3.75 32 3.00 96 4.25 45
4.00 66 3.75 166 3.70 250 4.50 356 4.00 138 3.25 273 4.50 5
4.50 133 3.80 293 4.00 197 5.00 1,719 4.50 4 3.50 367 5.00 143
5.00 574 4.00 795 4.50 352 5.25 11 5.00 1,388 3.60 6 5.25 25
5.50 100 4.50 1 4.75 2 6.00 2,396 5.25 1 3.75 2 6.00 1,150
6.00 381 5.00 800 5.00 814 6.50 31 6.00 357 4.00 629 6.25 9
6.50 5 5.25 80 5.25 1 7.00 1,327 7.00 924 4.50 200 6.50 112
7.00 641 5.50 7 6.00 257 7.50 1,553 7.50 555 5.00 818 7.00 1,721
7.50 523 6.00 210 6.50 220 8.00 1,492 8.00 1,618 5.40 9 7.50 434
8.00 915 6.30 2 7.00 176 8.25 1 8.25 6 5.50 80 8.00 1,027
8.25 1 6.40 9 7.50 5 8.50 1 8.50 1,591 5.85 4 8.50 1,943
8.50 18 6.50 17 7.75 1 8.75 5 8.75 1 6.00 908 9.00 566
8.75 13 6.65 1 7.90 1 9.00 3,102 9.00 2,150 6.25 1 9.50 350
9.00 2,334 6.75 1 8.00 1,611 9.50 780 9.25 357 6.38 3 10.00 544
;
run;

proc sort data = in_sales;
by price descending y s;

proc transpose data=in_sales (drop=s y) out=sales(drop=_name_);
by price ;
id season;

Re: multi colums sort and imput the results into new single column

ssas — Fri, 03 Apr 2009 11:09:46 GMT

Hi Mate,
they are price price1 price2 price3......
in the source data set
but at my output data set i want to see only one price coulmn and its respective aut spr summer values

Thanks Message was edited by: sams1

Re: multi colums sort and imput the results into new single column

sbb — Fri, 03 Apr 2009 20:37:02 GMT

Did you even try using the example SAS code that was provided by another forum subscriber?

Scott Barry
SBBWorks, Inc.