Need to create multiple rows from one based on a start and end date.

[pokerpercal]

Right now I have a list of subscribers to a product. Each subscriber has two fields indicating an effective and term date (named date_eff and date_term), such as 01JUL2003 and 31JAN2004. I would like to create a variable that is Month_Paid for each month in that range, so instead of having one row for that subscriber, I would now have 7 rows with the Month_Paid field being 01JUL2003, 01AUG2003, …, 01DEC2003, 01JAN2004.

I've tried a few do loops that I read about on the forum but can't seem to find the correct logic.

Thanks!

[Reeza]

Please show what you’ve tried so far. The logic is use INTCK() to determines the number of months and then loop with that. INTNX() will create the dates.

[pokerpercal]

I used the follow code:

data want_month;

set spanMOC;

date=dateeff;

do while (date<=dateterm);

output;

date=intnx('month', date, 1, 's');

end;

run;

This code will create the 7 rows I am looking for, the problem is that I cant figure out the concise way to create the new column that gives the month and in the format I described in the original post.

[Reeza]

Not sure what you mean by ‘I can’t figure out the concise way to create the new column that gives the month and format’?

Doesn’t renaming date to Month_Paid fix that? Otherwise please show a small example of input and desired output.

[pokerpercal]

That worked, I had messed up a variable in the loop which was causing the code to run indefinitely. Fixed that and formatted the month field.

Thanks!

[VDD]

try the INTGET