topic Re: Need to create multiple rows from one based on a start and end date. in New SAS User

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

pokerpercal — Tue, 06 Nov 2018 19:45:34 GMT

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!

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

Reeza — Tue, 06 Nov 2018 20:03:08 GMT

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.

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

pokerpercal — Tue, 06 Nov 2018 20:08:50 GMT

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.

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

Reeza — Tue, 06 Nov 2018 20:29:30 GMT

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.

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

VDD — Wed, 07 Nov 2018 00:24:57 GMT

try the INTGET

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

pokerpercal — Wed, 07 Nov 2018 14:08:08 GMT

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!