BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
polingjw
Quartz | Level 8
Message was edited by: polingjw Message was edited by: polingjw
polingjw
Quartz | Level 8
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
Not applicable
Very well done that man, thats exactly what I needed

Thank you so much!
Rgds
data_null__
Jade | Level 19
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]

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 692 views
  • 0 likes
  • 3 in conversation