Get list of dates between to dates and their day count

[jmmedina252]

Hi, I need to get a list of dates between an members start date and end date and the day count they would be.  The data looks like this:

Member   Start_date   End_date  

100          19MAY21     25MAY21

103          01MAY21     04MAY21

105          03MAY21     04MAY21

and the output data would be like this with all dates in between the start and end date per member:

Member   Start_date   End_date   Date           Date_Count

100          19MAY21     25MAY21   19MAY21   0

100          19MAY21     25MAY21   20MAY21   1

100          19MAY21     25MAY21   21MAY21   2

100          19MAY21     25MAY21   22MAY21   3

100          19MAY21     23MAY21   23MAY21   4

103          01MAY21     04MAY21   01MAY21   0

103          01MAY21     04MAY21   02MAY21   1

103          01MAY21     04MAY21   03MAY21   2

103          01MAY21     04MAY21   04MAY21   3

105          03MAY21     04MAY21   03MAY21   0

105          03MAY21     04MAY21   04MAY21   1

Note: Start_date is day 0

[mklangley · Accepted solution]

Here's one way: Looping from start_date to end_date for each member, and outputting a new row each time.

data have;
    input member start_date :date9. end_date :date9.;
    format start_date end_date date9.;
    datalines;
100 19MAY21 25MAY21
103 01MAY21 04MAY21
105 03MAY21 04MAY21
run;

data want (drop=i);
    set have;
    by member;
    retain i 0;
    format date date9.;
    if first.member then i=0;
    date = start_date;
    do while (date < end_date);
        date = intnx('day',start_date,i);
        date_count = i;
        i = i+1;
        output;
    end;
run;

proc print; run;

Result:

[Kurt_Bremser]

A simple iterative DO loop will do:

data want;
set have;
format date date9.;
do date = start_date to end_date;
  date_count = date - start_date;
  output;
end;
run;