Search code examples
sasproc-sql

Creating dummy date records between two date ranges


Hello I need to create dummy date records per subjid. So for example 10001 start date (ASTDT) is 01DEC2019 and End date (AENDT) IS 03DEC2019 so I need to create a dummy record for 02DEC2019 for that subject ID. I'm assuming this may require a proc transpose at some point and I've seen relevant code with similar logic like this but not exactly what I need

data want;
set have;
by account;

output;

if last.account then do;
    /*Current month as a number*/
    month_n = month(input(catt("01",strip(month),"2000"),date9.));
    /*LastMonth as a number*/
    to_month = month(input(catt("01",lastMonth,"2000"),date9.));

    do i=month_n+1 to to_month;
        month = put(mdy(i,1,2000),monname3.); /*Increment the month and write the month name*/
        output;
    end;
end;

drop month_n to_month i;
run;

This is the desired output


100001 01DEC2019 03DEC2019
100001 02DEC2019 03DEC2019
100001 03DEC2019 03DEC2019
100002 30JAN2020 31JAN2020
100002 31JAN2020 31JAN2020
100002 31JAN2020 02FEB2020
100002 01FEB2020 02FEB2020
100002 02FEB2020 02FEB2020
100002 31JAN2020 31JAN2020
100003 24FEB2020 24FEB2020
100003 21FEB2020 22FEB2020
100003 22FEB2020 22FEB2020

Solution

  • data want(drop = ASTDT);
       format subjid dt AENDT;
       set have;
       do dt = ASTDT to AENDT;
          dummy = not (dt = ASTDT | dt = AENDT);
          output;
       end;
       format dt date9.;
    run;
    

    Result:

    Obs subjid dt        AENDT     dummy 
    1   100001 01DEC2019 03DEC2019 0 
    2   100001 02DEC2019 03DEC2019 1 
    3   100001 03DEC2019 03DEC2019 0 
    4   100002 30JAN2020 31JAN2020 0 
    5   100002 31JAN2020 31JAN2020 0 
    6   100002 31JAN2020 02FEB2020 0 
    7   100002 01FEB2020 02FEB2020 1 
    8   100002 02FEB2020 02FEB2020 0 
    9   100002 31JAN2020 31JAN2020 0 
    10  100003 24FEB2020 24FEB2020 0 
    11  100003 21FEB2020 22FEB2020 0 
    12  100003 22FEB2020 22FEB2020 0