Experienced SAS user needing to know how to turn a dataset around??

[deleted_user]

Hi I'm an experienced user of SAS but have encountered a problem I've never faced before.

Basically I have a dataset that looks something like this:

Product Open_date Amount_0111 Amount_0211 Amount_0311 Create_0111 Create_0211 Create_0311.

Where the balance and limit variables are in the format Balance_mmyy where mmyy is open date

However what I want the dataset to look like is this:

Product Open_date Month Amount Create
Blah 01/01/2010 1 100 1000
Blah 01/01/2010 2 150 1000
Blah 01/01/2010 3 180 1000
Blah2 01/01/2010 1 100 1000
Blah2 01/01/2010 2 150 1000
Blah2 01/01/2010 3 180 1000


where month is number of months since the product was opened.

Can anyone shed any light?

Thanks

[polingjw]

Message was edited by: polingjw Message was edited by: polingjw

[polingjw]

I'm going to try pasting the code again. I think that my original posting was changed because it had an "i" enclosed in brackets.

[pre]
data old;
input Product $ Open_date:mmddyy10. Amount_0111 Amount_0211 Amount_0311 Create_0111 Create_0211 Create_0311;
format open_date mmddyy10.;
datalines;
blah 01/01/2010 100 150 180 1000 1000 1000
blah2 02/01/2010 100 150 180 1000 1000 1000
;
run;

data new;
set old;
array amounts{*} amount:;
array creates{*} create:;
do k=1 to dim(amounts);
amount = amounts{k};
month = scan(vname(amounts{k}), 2, '_');
do j=1 to dim(creates);
if month=scan(vname(creates), 2, '_') then do;
create=creates{j};
leave;
end;
end;
output;
end;
keep product open_date month amount create;
run;

proc print data=new noobs;
var product open_date month amount create;
run;

[/pre]

[deleted_user]

Very well done that man, thats exactly what I needed

Thank you so much!
Rgds

[data_null__]

You might consider a 2 PROC TRANSPOSE solution, aka flip and half flop. You don't have to fiddle with arrays and the same code works for two variables as you have or any number of variables of the samve type and naming convention.

[pre]
data test;
input Product:$8. Open_date:mmddyy8. Amount_0111 Amount_0211 Amount_0311 Create_0111 Create_0211 Create_0311;
format o: date.;
cards;
Blah 01012011 100 150 180 1000 1000 1000
Blah2 01012011 100 150 180 1000 1000 1000
;;;;
run;
proc print;
run;
proc transpose data=test out=flip;
by product open_date;
run;
data tall;
set tall;
month = intck('month',open_date,input(cats('01',scan(_name_,-1,'_')),ddmmyy.));
_name_ = scan(_name_,1,'_');
run;
proc sort data=tall;
by product open_date month _name_;
run;
proc transpose data=tall out=halfflop;
by product open_date month;
run;
proc print;
run;
[/pre]

[pre]
Open_
Obs Product date month _NAME_ Amount Create

1 Blah 01JAN11 0 COL1 100 1000
2 Blah 01JAN11 1 COL1 150 1000
3 Blah 01JAN11 2 COL1 180 1000
4 Blah2 01JAN11 0 COL1 100 1000
5 Blah2 01JAN11 1 COL1 150 1000
6 Blah2 01JAN11 2 COL1 180 1000
[/pre]