topic Re: re: macro do loop in SAS Programming

re: macro do loop

twildone — Fri, 16 Aug 2019 20:27:18 GMT

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

Re: re: macro do loop

PaigeMiller — Fri, 16 Aug 2019 19:55:01 GMT

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;

Re: re: macro do loop

novinosrin — Fri, 16 Aug 2019 19:59:38 GMT

@PaigeMiller slick explanation on target Sir. Very neat!

Re: re: macro do loop

Tom — Fri, 16 Aug 2019 20:13:29 GMT

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?

Re: re: macro do loop

Tom — Fri, 16 Aug 2019 20:15:22 GMT

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.

Re: re: macro do loop

Tom — Fri, 16 Aug 2019 20:21:46 GMT

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

Re: re: macro do loop

twildone — Fri, 16 Aug 2019 20:32:06 GMT

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