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!
... View more