topic Iterate through dates in SAS Programming

Iterate through dates

Gluttony — Thu, 23 Apr 2020 16:38:41 GMT

Hi,

I have a macro (Proc SQL) which takes a date as an input (e.g. '31/03/2020'), and I want to execute this macro on several dates.

I tried a %do loop on a list of dates (e.g. '31/01/2020' '31/03/2020'), but it seems that it is not recognized as a date when I use the scan function.

Anyone has an idea on how to declare this list of dates and how to iterate through it ?

Thank you,

Clément

Re: Iterate through dates

ballardw — Thu, 23 Apr 2020 17:15:17 GMT

Please show the code you attempted.

If you want to have a literal date provided as value then it likely needs to appear as "01JAN2020"D unless your variable is character,

Macro %do iterators do not allow lists of values like the data step Do loop. Adjustments to get something similar to what you want requires having some basic code.

Scan will not recognize anything as a "date" since it is a character function.

Re: Iterate through dates

Reeza — Thu, 23 Apr 2020 17:17:29 GMT

CALL EXECUTE()?

data demo;

do i='31Jan2020'd to '31Mar2020'd;
%*creates a string that looks like myMacro("31/01/2020");;;
str = catt('%myMacro("', put(i, ddmmyys10.), '");');
*runs the macro;
call execute(str);
end;

run;

Re: Iterate through dates

ghosh — Thu, 23 Apr 2020 17:20:09 GMT

here is how I would declare a list of dates

/* Specify start and end dates*/
%let start_date=%str(31JAN2020);
%let end_date=%str(31MAR2020);

data mnthrnge;
	date_N="&start_date"d;

	do while (date_N<="&end_date"d);
		output;
		date_N=intnx('month', date_N, 1, 's');
	end;
	format date_N date9.;
run;

Using for example, a Call Execute or DOSUBL construct, you can cycle through the dates and run your code for each month

Re: Iterate through dates

Tom — Thu, 23 Apr 2020 18:17:32 GMT

What types of values does the macro want? All macro parameters are strings, so how the macro code uses the parameter determines what types of strings it can handle.

What does your list of dates look like? What you showed were character strings. The date value for the last day of march 2020 is the number 22,005 since that is how many days since the start of 1960. You can represent a specific date in SAS code with a date literal. A quoted string tht the DATE informat can interpret followed by the letter d. "31MAR2020"d for example.

Re: Iterate through dates

Gluttony — Fri, 24 Apr 2020 08:42:07 GMT

Thank you,

After reading your answers, it turns out that my SQL ("SELECT ... WHERE DATE IN ( &date.)") needs $date. to be in these following formats : '31/03/2020' or '31-03-2020', which seem to be strings in SAS (but recognized as date by Oracle).

My macro selects data from DB, processes them and exports an excel file.

How can I execute this macro on several strings ? ('31/01/2020' '31/03/2020')

Ex :

%LET list_dates = '31/01/2020','31/03/2020';

%macro main(list_dates);
%do i=1 %to 2;
%macroSQL(%scan(&list_dates.,&i,','));
%end;
%mend;

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
'31/03/2020' 
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR: The macro macroSQL will stop executing.

does not seem to work

%macroSQL('31/01/2020');

But this works

Re: Iterate through dates

Gluttony — Fri, 24 Apr 2020 08:55:47 GMT

Ok, I tried a different separator in the list, removed the quotes in the scan function and it worked !!

%LET list_dates = '31/01/2020'-'31/03/2020';

%macro main(list_dates);
%do i=1 %to 2;
%macroSQL(%scan(&list_dates.,&i,-));
%end;
%mend;

Thank you all,

Gluttony