Create record per month

[Jaap_K]

Hi,

I have a dataset that I want to split based on 2 date columns. In this new column, the date for the intervening months should become the last day of that month
Example;
Start date             Enddate
03Jan2022           05may2022

Result:

Start date             Enddate               NewColumn
03Jan2022           05may2022        03jan2022

03Jan2022           05may2022        28feb2022

03Jan2022           05may2022        31mar2022

03Jan2022           05may2022        30apr2022

03Jan2022           05may2022        05may2022

Thanks for your help!

[PaigeMiller · Accepted solution]

This solution assumes that the values of startdate and enddate are valid numeric SAS date values.

data have;
    startdate='03JAN2022'd;
    enddate='05MAY2022'd;
run;
data want;
    set have;
    diff_months=intck('month',startdate,enddate);
    do i=0 to diff_months;
        if i=0 then newcolumn=startdate;
        else if i=diff_months then newcolumn=enddate;
        else newcolumn=intnx('month',startdate,i,'e');
        output;
    end;
    drop i diff_months;
    format newcolumn date9.;
run;