CODEWITHSUNDEEP
sasproc-sql
smackersz88
smackersz88

Reputation: 93

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

Upvotes: 0

Views: 164

Answers (3)

PeterClemmensen
PeterClemmensen

Reputation: 4947

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

Upvotes: 0

PeterClemmensen
PeterClemmensen

Reputation: 4947

See if this meets your needs

data have;
input subjid $ (ASTDT AENDT)(:date9.);
format ASTDT AENDT date9.;
datalines;
100001 01DEC2019 03DEC2019
100002 30JAN2020 31JAN2020
100002 31JAN2020 02FEB2020
100002 31JAN2020 31JAN2020
100003 24FEB2020 24FEB2020
100003 21FEB2020 22FEB2020
;

data want;
   set have;
   do ASTDT = ASTDT to AENDT;
      output;
   end;
run;

Result:

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

Upvotes: 1

PeterClemmensen
PeterClemmensen

Reputation: 4947

For starters, let us assunme that your data looks like below.

What does your desired result look like?

data have;
input subjid $ (ASTDT AENDT)(:date9.);
format ASTDT AENDT date9.;
datalines;
100001 01DEC2019 03DEC2019
100002 30JAN2020 31JAN2020
100002 31JAN2020 02FEB2020
100002 31JAN2020 31JAN2020
100003 24FEB2020 24FEB2020
100003 21FEB2020 22FEB2020
;

Upvotes: 1

Related Questions