Site icon 9TO5SAS

How to Loop through Dates in SAS?

In this example, you’ll see how to use a SAS macro and a Do loop in the Data step to loop through dates in SAS.

Using the INTNX() function, we can loop over dates based on an offset value. INTCK() can be used to determine the number of months to generate

Using the Data step to loop through dates

The INTNX() function is used to loop through dates based on an offset. For example, the INTCK() can be used to determine how many months to generate.

%let start_dt = '01jul2022'd;
%let stop_dt = '01dec2022'd;

data datelist;
diff=intck('month',&start_dt,&stop_dt);
	do i= 0 to diff;
	newdt=intnx('month',&start_dt,i,'b');
		output;
	end;
	format newdt date9.;
	drop i diff ;
run;

With the %LET statement, you can create a macro variable named &start_dt and &stop_dt.

The INTCK function returns the months between &start_dt and &stop_dt; this value is stored in the diff variable. The MONTH function is the first argument to retrieve the month interval.

diff=intck('month',&start_dt,&stop_dt);

Do loop is used to loop through the number of months between &start_dt and stop_dt.

The INTNX function increments the &start_dt date by MONTH. The B argument indicates that the returned date or DateTime value begins at the beginning of the interval. 

	do i= 0 to diff;
	newdt=intnx('month',&start_dt,i,'b');
		output;
	end;

Loop through Dates Using a Macro %DO Loop

Using this macro, you can loop through a starting and ending date by a month.

%macro date_loop(start,end);
   %let start=%sysfunc(inputn(&start,anydtdte9.));
   %let end=%sysfunc(inputn(&end,anydtdte9.));
   %let dif=%sysfunc(intck(month,&start,&end));
     %do i=0 %to &dif;
      %let date=%sysfunc(intnx(month,&start,&i,b),date9.);
      %put &date;
     %end;
   %mend date_loop;

   %date_loop(01jul2015,01feb2016)

We use the %LET statement to create the value of the macro variable named &start and %end.

%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));

INPUTN can be used to apply ANYDTDTE. format to any value passed to &start and &end macro variables.ANYDTDTE. format results in SAS date format for &START and &end.

Use the %let statement to create a macro variable named &dif. The INTCK function returns the months between &start and &end. The MONTH function is the first argument to retrieve the month interval.

%let dif=%sysfunc(intck(month,&start,&end));

Note that when using this function within the macro facility, quotation marks are not used around MONTH, like you would in the DATA step.

The %do statement is used to loop through the number of months (&dif) between &start and &end.

%do i=0 %to &dif;
      %let date=%sysfunc(intnx(month,&start,&i,b),date9.);
      %put &date;
     %end;

The INTNX function increments the &start date by MONTH. 

The B argument indicates that the returned date or DateTime value begins at the beginning of the interval. 

The second argument to %SYSFUNC specifies the format DATE9 for the value returned from INTNX. 

Date values are written to the log using the %PUT statement.

Exit mobile version