Transposing data

[kushiwije]

Hello,

I have a data set which is set up such that the store numbers - dates - product numbers are going down and the dollar sales and volume sales going across. I have 310 contiguos products and I want these products to go across in the data set. So I would like the transformed data set to look like below. I tried using arrays but it seems that it won't let me use up to 310 as suffix becos my job is bombing when I do so. When I test the same code on 10 products it works just fine. So if you know anyway to overcome this problem I would really appreciate it.

CURRENT DATA SET:
=================

store date product vol dol
==== === ===== == ==
1 jan 1 xx xx
1 jan 2 xx xx
1 jan 310 xx
1 feb 1
1 feb 2
1 feb 310
2 jan 1 xx xx
2 jan 2 xx xx
2 jan 310 xx
2 feb 1
2 feb 2
2 feb 310


THIS IS HOW I WOULD LIKE TO SEE THE NEW DATA SET:
=================================================

store date vol_1 vol_2 ..vol_310 dol_1 dol_2 .. dol_310
===== === ==== === === === === ===
1 jan xx xx
1 feb xx xx
1 mar xx xx
2 jan xx xx
2 feb xx xx
2 mar xx xx

Thanks
KushiWije

 

[ChrisW75]

Use Proc Transpose:-

proc transpose data=dset1 out=dset2 prefix=vol_;
  by store date;
  var vol
  ID product;
run;

proc transpose data=dset1 out=dset2 prefix=dol_;
  by store date;
  var dol
  ID product;
run;

Then join the 2 datasets back together by store and Date. You can't unfortunately transpose multiple variables at the same time.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.