BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gluttony
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Gluttony
Fluorite | Level 6

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

View solution in original post

6 REPLIES 6
ballardw
Super User

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.

Reeza
Super User

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;
ghosh
Barite | Level 11

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

Tom
Super User Tom
Super User

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. 

Gluttony
Fluorite | Level 6

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

Gluttony
Fluorite | Level 6

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1096 views
  • 4 likes
  • 5 in conversation