Duplicating one row into multiple rows but changing one variable

[comm77]

Hi Everyone,

I'm having trouble transforming the following dataset into the shown target output. I would like to split each ID into its respective whole month period between the start date and the end date. The actual dataset I'm using has about 20 variables, most of which are factors like colour, size and type which I'd like to remain the same in each of the new transformed row (like the "Colour" column in the example below). 

Dataset

ID    Start_dt       End_dt       Colour     Size         Type   ...

1     13May17     13May18    Blue        Large       Flex     ...

2     17Sep16     5Jun17       Green      Medium   Soft    ...

Want

ID    Start_dt       End_dt        Period          Colour   ...

1     13May17     13May18     13May17      Blue      ...

1     13May17     13May18     13Jun17       Blue      ...

1     13May17     13May18     13Jul17        Blue      ...

1     13May17     13May18     13Aug17      Blue      ...

1     13May17     13May18     13Sep17      Blue      ...

1     13May17     13May18     13Oct17       Blue      ...

1     13May17     13May18     13Nov17      Blue      ...

1     13May17     13May18     13Dec17      Blue      ...

1     13May17     13May18     13Jan18       Blue      ...

1     13May17     13May18     13Feb18      Blue      ...

1     13May17     13May18     13Mar18      Blue      ...

1     13May17     13May18     13Apr18       Blue      ...

2     17Sep16     5Jun17         17Sep16     Green    ...

2     17Sep16     5Jun17         17Oct16      Green    ...

2     17Sep16     5Jun17         17Nov16     Green    ...

2     17Sep16     5Jun17         17Dec16     Green    ...

2     17Sep16     5Jun17         17Jan17      Green    ...

2     17Sep16     5Jun17         17Feb17      Green    ...

2     17Sep16     5Jun17         17Mar17      Green    ...

2     17Sep16     5Jun17         17Apr17       Green    ...

2     17Sep16     5Jun17         17May17      Green    ...

My initial thought process was to first temporarily create a variable n to count the number of whole months between the two date variables then apply the do statement to create the monthly increment variable, period and using the retain function to duplicate the original row values into the new row. 

data have1;

set have;

n=intck('month', Start_dt, End_dt);

run;

data want;

set have1;

retain period;

if n ge 1 then do n = 1  to n;

period = intck('month', Start_dt, n, 's');

end;

run;

Its my first time using the retain function (to keep all the original constant in the new rows) but I can't seem to duplicate rows at all (probably something I'm doing wrong). I'd like to know your thoughts on my approach and if you know how to do the above in a simpler way. Appreciate your time and effort in helping me!

[novinosrin · Accepted solution]

data have;
input ID   ( Start_dt       End_dt ) (:date7.)      (Colour     Size         Type ) ($);
format Start_dt       End_dt  date9.;
cards; 
1     13May17     13May18    Blue        Large       Flex     ...
2     17Sep16     5Jun17       Green      Medium   Soft    ...
;


data want;
set have;
period=Start_dt;
do while(period<End_dt);
 output;
 period=intnx('mon',Period,1,'s');
end;
format period date9.;
run;

[comm77]

Thanks for your quick reply! I'll test it out later this afternoon and get back to you 🙂