re: macro do loop

[twildone]

Hi...I am trying to get check each date in the temp dataset to see whether or not it falls between the StartDate and EndDate for each record in the have1 dataset. If it does, I would like to increase the EndDate by 1 and if this EndDate falls on the weekend, I would like to increase the EndDate to the following Monday. I am getting the same EndDates…..thanks

data temp;
input datex :yymmdd10.;
format datex yymmddd10.;
datalines;
2019-01-15 
2016-08-25 
2019-04-22 
2017-09-15 
2018-11-02
;
run;

data have1;
input ID 1. StartDate :yymmdd10. EndDate :yymmdd10.;
format ID 1. StartDate :yymmdd10. EndDate :yymmdd10.;
datalines;
1 2017-01-01 2018-12-30
2 2016-01-01 2017-12-30
3 2015-01-01 2016-12-30
4 2017-09-01 2017-12-30
5 2018-11-01 2019-12-30
;
run;

proc sql noprint;
	select count(datex) into :countList separated by ' '  	    
	from work.temp;		   
quit;
run;
%put &countList;

proc sql noprint;
	select datex into :dateList separated by ' '  	    
	from work.temp;		   
quit;
run;
%put &dateList;
%macro check;
data want;
set have1;
by ID;
%do i=1 %to i=&countList;
  %if StartDate <= &dateList <= EndDate
  %then %do;
    EndDate = EndDate + 1;
	%if weekday(EndDate) in (1,7) %then 
	EndDate = EndDate + 2;
%end;
%output;
%return;
%end;
run;
%mend check;
%check;

ID	StartDate	EndDate
1	2017-01-01	2018-12-31
2	2016-01-01	2018-01-02
3	2015-01-01	2017-01-02
4	2017-09-01	2018-01-02
5	2018-11-01	2019-12-31

[PaigeMiller]

No macros needed. This is a Cartesian join, or to phrase it another way, you want to test every possible combination of records in temp and have1, and keep the ones where datex is between startdate and enddate. This may take a very long time depending on the size of your input data set. I show how to increase the enddate by 1. I leave it up to you to modify this to take care of weekends.

data every_combination;
    set temp;
	do i=1 to n;
	    set have1 point=i nobs=n;
		if startdate<datex<enddate then do;
			enddate=enddate+1;
			output ;
		end;
	end;
run;

[novinosrin]

@PaigeMiller  slick explanation on target Sir. Very neat!

[Tom]

Please show the results that you want for that input.

Are you expecting 5 time 5 = 25 observations?  Or something else?

What about values of ENDDATE that already fall on a week-end?  What value do you want for those when DATEX is NOT between STARTDATE and ENDDATE?

[Tom]

You have some nonsense code in there. 

What do you think the macro processor is going to do with this statement?  

%if weekday(EndDate) in (1,7) %then 

The letters WEE.... are never going to be equal to the digit 1 or the digit 7.

[Tom · Accepted solution]

Is this what you want?

data temp;
  date_id +1;
  input datex :yymmdd.;
  format datex yymmddd10.;
datalines;
2019-01-15 
2016-08-25 
2019-04-22 
2017-09-15 
2018-11-02
;

data have1;
  input ID StartDate :yymmdd. EndDate :yymmdd.;
  format StartDate EndDate yymmdd10.;
datalines;
1 2017-01-01 2018-12-30
2 2016-01-01 2107-12-30
3 2015-01-01 2016-12-30
4 2017-09-01 2017-12-30
5 2018-11-01 2019-12-30
;

proc sql noprint ;
  create table want as 
    select * 
         , (StartDate <= datex <= EndDate) as is_between
         , max(EndDate,intnx('weekday',EndDate,calculated is_between))
           as new_Enddate format=yymmdd10.
   from temp,have1
   ;
quit;

proc print;
run;

                                                                                      new_
Obs    date_id         datex    ID     StartDate       EndDate    is_between       Enddate

  1       1       2019-01-15     1    2017-01-01    2018-12-30         0        2018-12-30
  2       1       2019-01-15     2    2016-01-01    2107-12-30         1        2108-01-02
  3       1       2019-01-15     3    2015-01-01    2016-12-30         0        2016-12-30
  4       1       2019-01-15     4    2017-09-01    2017-12-30         0        2017-12-30
  5       1       2019-01-15     5    2018-11-01    2019-12-30         1        2019-12-31
  6       2       2016-08-25     1    2017-01-01    2018-12-30         0        2018-12-30
  7       2       2016-08-25     2    2016-01-01    2107-12-30         1        2108-01-02
  8       2       2016-08-25     3    2015-01-01    2016-12-30         1        2017-01-02
  9       2       2016-08-25     4    2017-09-01    2017-12-30         0        2017-12-30
 10       2       2016-08-25     5    2018-11-01    2019-12-30         0        2019-12-30
 11       3       2019-04-22     1    2017-01-01    2018-12-30         0        2018-12-30
 12       3       2019-04-22     2    2016-01-01    2107-12-30         1        2108-01-02
 13       3       2019-04-22     3    2015-01-01    2016-12-30         0        2016-12-30
 14       3       2019-04-22     4    2017-09-01    2017-12-30         0        2017-12-30
 15       3       2019-04-22     5    2018-11-01    2019-12-30         1        2019-12-31
 16       4       2017-09-15     1    2017-01-01    2018-12-30         1        2018-12-31
 17       4       2017-09-15     2    2016-01-01    2107-12-30         1        2108-01-02
 18       4       2017-09-15     3    2015-01-01    2016-12-30         0        2016-12-30
 19       4       2017-09-15     4    2017-09-01    2017-12-30         1        2018-01-01
 20       4       2017-09-15     5    2018-11-01    2019-12-30         0        2019-12-30
 21       5       2018-11-02     1    2017-01-01    2018-12-30         1        2018-12-31
 22       5       2018-11-02     2    2016-01-01    2107-12-30         1        2108-01-02
 23       5       2018-11-02     3    2015-01-01    2016-12-30         0        2016-12-30
 24       5       2018-11-02     4    2017-09-01    2017-12-30         0        2017-12-30
 25       5       2018-11-02     5    2018-11-01    2019-12-30         1        2019-12-31

 Or do you want it collapsed somehow?

proc sql noprint ;
  create table want as 
    select id, startdate, enddate
         , sum(StartDate <= datex <= EndDate) as days_between
         , max(EndDate,intnx('weekday',EndDate,calculated days_between))
           as new_Enddate format=yymmdd10.
   from temp , have1
   group by id, startdate, enddate
   ;
quit;

                                          days_           new_
Obs    ID     StartDate       EndDate    between       Enddate

 1      1    2017-01-01    2018-12-30       2       2019-01-01
 2      2    2016-01-01    2107-12-30       5       2108-01-06
 3      3    2015-01-01    2016-12-30       1       2017-01-02
 4      4    2017-09-01    2017-12-30       1       2018-01-01
 5      5    2018-11-01    2019-12-30       3       2020-01-02

[twildone]

Thanks Tom....It was the second approach that I really want....Awesome!!