I am new to macros. I am trying to write a pragram that will create multiple datasets using a different starting and ending dates for each iteration. So I am using enterprise guide 6.1 sas version 9.4 64 bit. This is what I created. It is suppose to create two different dates for the start date and end date. The first date is last month the 2nd, and the second date is the current month 1st. These should flow to pro sql that looks for the beginning and ending dates, does the process, and then loops until all beginning and ending dates are completed. I tried running it with just 2 to 2 and 1 to 1 just to test it. It came back with no errors and no output.
Options mprint symbolgen;
%DO_CYCLE;
%DO I = 2 %TO 2; *29;
%DO J = 1 %TO 1; *28;
data _null_;
call symputx('st', PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),-1,'b'),&I), date9.));
call symputx('end', PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),0,'b'),&J), date9.));
RUN;
*** Accounts that are closing in the current month Placement Data****;
proc sql;
create table get_accts.&i as
select a.creditaccountID, a.cycleclosedate as cyclecdate, a.cycleopendate as cycleodate, b.agency,
a.billingcycle, a.dimbucketidOpening as bucket
from place.FactPlacement a inner join place.dimAgency b ON a.dimAgencyIDOpening = b.dimagencyid
where a.cycleOpendate eq "&st"d
and a.cycleCloseDate eq "&end"d
and a.reportingdate = a.cycleCloseDate
and a.dimAgencyIDOpening in (5,6,7,8,9,10,11,13,14,15,16,22,28,29,31,32,34,35,36,37,38,42,49,50,52)
and a.dimbucketid ge 5;
QUIT;
%END; %END;
%MEND DO_CYCLE;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 Options mprint symbolgen;
MPRINT(DO_CYCLE): *29;
MPRINT(DO_CYCLE): *28;
24 %DO_CYCLE;
MPRINT(DO_CYCLE): data _null_;
SYMBOLGEN: Macro variable I resolves to 2
MPRINT(DO_CYCLE): call symputx('st', PUT(intnx('DAY',intnx('MONTH',20767,-1,'b'),2), date9.));
SYMBOLGEN: Macro variable J resolves to 1
MPRINT(DO_CYCLE): call symputx('end', PUT(intnx('DAY',intnx('MONTH',20767,0,'b'),1), date9.));
MPRINT(DO_CYCLE): RUN;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MPRINT(DO_CYCLE): *** Accounts that are closing in the current month Placement Data****;
MPRINT(DO_CYCLE): proc sql;
SYMBOLGEN: Macro variable I resolves to 2
NOTE: Line generated by the macro variable "I".
24 get_accts.2
_
22
200
ERROR: Librefs are restricted to eight characters. get_accts has been truncated.
SYMBOLGEN: Macro variable ST resolves to 03OCT2016
SYMBOLGEN: Macro variable END resolves to 02NOV2016
MPRINT(DO_CYCLE): create table get_accts.2 as select , a.cycleclosedate as cyclecdate, a.cycleopendate as cycleodate, b.agency,
a.billingcycle, a.dimbucketidOpening as bucket from place.FactPlacement a inner join place.dimAgency b ON a.dimAgencyIDOpening =
b.dimagencyid where a.cycleOpendate eq "03OCT2016"d and a.cycleCloseDate eq "02NOV2016"d and a.reportingdate = a.cycleCloseDate and
a.dimAgencyIDOpening in (5,6,7,8,9,10,11,13,14,15,16,22,28,29,31,32,34,35,36,37,38,42,49,50,52) and a.dimbucketid ge 5;
ERROR 22-322: Expecting a name.
2 The SAS System 09:15 Wednesday, November 9, 2016
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(DO_CYCLE): QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
25 %DO I = 2 %TO 2; *29;
ERROR: The %DO statement is not valid in open code.
26 %DO J = 1 %TO 1; *28;
ERROR: The %DO statement is not valid in open code.
27
28 data _null_;
29 call symputx('st', PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),-1,'b'),&I), date9.));
_
22
WARNING: Apparent symbolic reference I not resolved.
30 call symputx('end', PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),0,'b'),&J), date9.));
_
22
WARNING: Apparent symbolic reference J not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, INPUT, PUT.
31 RUN;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
32
33 *** Accounts that are closing in the current month Placement Data****;
34 proc sql;
35 create table get_accts.&i as
_
22
WARNING: Apparent symbolic reference I not resolved.
35 create table get_accts.&i as
_
200
ERROR 22-322: Expecting a name.
ERROR 200-322: The symbol is not recognized and will be ignored.
36 select a.creditaccountID, a.cycleclosedate as cyclecdate, a.cycleopendate as cycleodate, b.agency,
ERROR: Librefs are restricted to eight characters. get_accts has been truncated.
37 a.billingcycle, a.dimbucketidOpening as bucket
38 from place.FactPlacement a inner join place.dimAgency b ON a.dimAgencyIDOpening = b.dimagencyid
39 where a.cycleOpendate eq "&st"d
WARNING: Apparent symbolic reference ST not resolved.
ERROR: Invalid date/time/datetime constant "&st"d.
3 The SAS System 09:15 Wednesday, November 9, 2016
40 and a.cycleCloseDate eq "&end"d
WARNING: Apparent symbolic reference END not resolved.
ERROR: Invalid date/time/datetime constant "&end"d.
41 and a.reportingdate = a.cycleCloseDate
42 and a.dimAgencyIDOpening in (5,6,7,8,9,10,11,13,14,15,16,22,28,29,31,32,34,35,36,37,38,42,49,50,52)
43 and a.dimbucketid ge 5;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
44 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
45 %END; %END;
ERROR: The %END statement is not valid in open code.
ERROR: The %END statement is not valid in open code.
46 %MEND DO_CYCLE;
ERROR: No matching %MACRO statement for this %MEND statement.
Inside a data step there is no need to use %sysfunc and likely will cause issues.
Also when dealing with dates unless a human is going to look at them there is no actual need to go through the additional code to create the text for a data literal:
data _null_;
call symputx('st' , intnx('DAY',intnx('MONTH',today(),-1,'b'),&I));
call symputx('end', intnx('DAY',intnx('MONTH',today(),0,'b'),&J));
RUN;
and then reference as
where a.cycleOpendate eq &st
&st and &end would have the numeric representations such as 20730
to get rid of your libref error change get_accts.&i to get_accts&i the . before any macro variable is literal text and not the macro concatenate operator.
If you post the log results in the window from clicking on the "run" icon in the forum message menu then formatting is preserved. The underscore that accompanies a 22-200 error is very helpful in showing where the expected variable name appears. Posting into the main box removes formatting.
The code or log doesn't show you actually calling the macro only compiling it.
Run it as follows and post the log.
Option mprint symbolgen;
%do_cycle;
Two notes here ...
First, you've posted different versions of the logs that would match different versions of the macro. Show us the latest version of each.
Second, be aware that your program would replace get_accts&i each time &J changes. You might need to re-think the name of the table you are creating.
Inside a data step there is no need to use %sysfunc and likely will cause issues.
Also when dealing with dates unless a human is going to look at them there is no actual need to go through the additional code to create the text for a data literal:
data _null_;
call symputx('st' , intnx('DAY',intnx('MONTH',today(),-1,'b'),&I));
call symputx('end', intnx('DAY',intnx('MONTH',today(),0,'b'),&J));
RUN;
and then reference as
where a.cycleOpendate eq &st
&st and &end would have the numeric representations such as 20730
to get rid of your libref error change get_accts.&i to get_accts&i the . before any macro variable is literal text and not the macro concatenate operator.
If you post the log results in the window from clicking on the "run" icon in the forum message menu then formatting is preserved. The underscore that accompanies a 22-200 error is very helpful in showing where the expected variable name appears. Posting into the main box removes formatting.
Thank you so much. That was very helpful.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.