Macro programming for dates

[pangea17]

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.

[Reeza]

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;

[Astounding]

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.

[ballardw · Accepted solution]

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.

[pangea17]

Thank you so much.  That was very helpful.