Duplicating one row into multiple rows but changing one variable

comm77 — Sat, 09 Nov 2019 00:41:36 GMT

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!

Re: Duplicating one row into multiple rows but changing one variable

novinosrin — Sat, 09 Nov 2019 00:49:29 GMT

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;

Re: Duplicating one row into multiple rows but changing one variable

comm77 — Sat, 09 Nov 2019 00:58:02 GMT

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

topic Re: Duplicating one row into multiple rows but changing one variable in SAS Programming

Duplicating one row into multiple rows but changing one variable

Re: Duplicating one row into multiple rows but changing one variable

Re: Duplicating one row into multiple rows but changing one variable