topic Re: Get list of dates between to dates and their day count in SAS Programming

Get list of dates between to dates and their day count

jmmedina252 — Fri, 21 Apr 2023 19:48:41 GMT

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

Re: Get list of dates between to dates and their day count

mklangley — Fri, 21 Apr 2023 20:13:15 GMT

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:

Re: Get list of dates between to dates and their day count

Kurt_Bremser — Fri, 21 Apr 2023 20:40:23 GMT

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;