topic Re: Create record per month in SAS Enterprise Guide

Create record per month

Jaap_K — Wed, 29 Nov 2023 11:11:34 GMT

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!

Re: Create record per month

PaigeMiller — Wed, 29 Nov 2023 12:21:20 GMT

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;