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
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:
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:
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.