topic Re: Problem with inserting DATE from a macro variable in SAS Programming

Problem with inserting DATE from a macro variable

Emoji — Tue, 03 Jan 2023 23:25:36 GMT

Hello Everyone

I am trying to run the following macro. The macro itself is working fine; however, there is an error when SAS executes DATE = &QUARTER_DAY_START TO &QUARTER_DAY_END; if I remove the "IF" and run the code, the log will be (at end of the code)

%MACRO AN_COST();
%LOCAL LOOP;
%LET LOOP = 1;

%DO %WHILE (&LOOP <= 4);
%LET DAYS= %EVAL( %SYSFUNC(INTNX(QTR,'01JAN2022'D, (&LOOP-1), E))-%SYSFUNC(INTNX(QTR,'01JAN2022'D, (&LOOP-1), B)) + 1);
%LET QUARTER_DAY_START = %SYSFUNC(DEQUOTE(" '%SYSFUNC(INTNX(MONTH, '01JAN2022'D, (&LOOP-1)*3, B), DATE11.)' "));
%LET QUARTER_DAY_END = %SYSFUNC(DEQUOTE(" '%SYSFUNC(INTNX(MONTH, '01JAN2022'D, ((&LOOP-1)*3)+ 2, E), DATE11.)' "));
%PUT &=DAYS;
%PUT &=QUARTER_DAY_START;
%PUT &=QUARTER_DAY_END;

DATA WORK.MY_NEW_TABLE;
SET WORK.MY_OLD_TABLE;

%IF MARKET = XXX %THEN
%DO;
DATE = &QUARTER_DAY_START TO &QUARTER_DAY_END;
OUTPUT;
%END;
RUN;

%LET LOOP=%SYSEVALF(&LOOP+1);
%END;
%MEND;

LOG:

13 DATE = &QUARTER_DAY_START TO QUARTER_DAY_END;
__
388
202
ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: Problem with inserting DATE from a macro variable

Kurt_Bremser — Tue, 03 Jan 2023 23:33:49 GMT

%IF MARKET = XXX %THEN /* this condition can never be true; the string MARKET is not equal to the string XXX */
%DO;
DATE = &QUARTER_DAY_START TO &QUARTER_DAY_END; /* this is an invalid data step statement, TO is not a numeric operator */ */
OUTPUT;
%END;

But why are you still doing this stupidity:

%LET QUARTER_DAY_START = %SYSFUNC(DEQUOTE(" '%SYSFUNC(INTNX(MONTH, '01JAN2022'D, (&LOOP-1)*3, B), DATE11.)' "));
%LET QUARTER_DAY_END = %SYSFUNC(DEQUOTE(" '%SYSFUNC(INTNX(MONTH, '01JAN2022'D, ((&LOOP-1)*3)+ 2, E), DATE11.)' "));

???

I have already shown you how to store dates in macro variable for later use in code.

Re: Problem with inserting DATE from a macro variable

Emoji — Tue, 03 Jan 2023 23:37:12 GMT

Thanks for the reply. Please be advised that if I manually enter the date as follows, the code will work without any issues:
if Market = 'MARKET_2022' then do Date = '01-JAN-2022'd to '31-MAR-2022'd;

Re: Problem with inserting DATE from a macro variable

ballardw — Tue, 03 Jan 2023 23:38:41 GMT

Post log in a text box opened with the </> icon at the top of the message window. Then the _ character will appear under the text that SAS says causes the syntax error.

As we mentioned in your previous post about INTNX formatted text values of dates are not actual date values and cannot be used in assignments or comparisons without extra work.

DATE = &QUARTER_DAY_START TO &QUARTER_DAY_END;

You are asking for something like

date = 01-JAN-2022 to 31-MAR-2022;

You do not have a data step DO loop but mix bits from what should be : Do date= <start value> to <end value>;

as %do; <macro instruction>

date = 01-JAN-2022 to 31-MAR-2022;

%end;

which is not a valid assignment statement for multiple reasons and not valid loop code either. You have to keep macro statements, %do / %end and data step statements do/end separate and know when you want which.

Did you have a working data step for a loop of dates before you began this? Look closely at it. If not, make sure you have one that works for a set of values and then compare with the code in your macro.

To debug your macro code as it generates run: Options Mprint; before running your macro. It will show the statements generated. If you have Logic issues in the macro include Mlogic and if you have creation / resolution of macro variables add Symbolgen to the options statement.

Re: Problem with inserting DATE from a macro variable

ballardw — Tue, 03 Jan 2023 23:40:32 GMT

@Emoji wrote:
Thanks for the reply. Please be advised that if I manually enter the date as follows, the code will work without any issues:
if Market = 'MARKET_2022' then do Date = '01-JAN-2022'd to '31-MAR-2022'd;

The code generated by your macro does not look like that. Use the Options MPRINT to confirm that.

Re: Problem with inserting DATE from a macro variable

Reeza — Tue, 03 Jan 2023 23:56:14 GMT

This is a flaw in logic and you're mixing macro and data step logic.

Market is a variable in your data set it seems and macro IF statement code will not access the variable.

What are you trying to do overall? Add records?

FYI - I actually doubt you need macro logic anywhere in this code but if you show a small example of what you're trying to accomplish we can help you simplify your code.

EDIT: Isn't that the wrong code as well from an earlier post for your macro variable creation?

@Emoji wrote:

Hello Everyone

I am trying to run the following macro. The macro itself is working fine; however, there is an error when SAS executes DATE = &QUARTER_DAY_START TO &QUARTER_DAY_END; if I remove the "IF" and run the code, the log will be (at end of the code)

%MACRO AN_COST();
%LOCAL LOOP;
%LET LOOP = 1;

%DO %WHILE (&LOOP <= 4);
%LET DAYS= %EVAL( %SYSFUNC(INTNX(QTR,'01JAN2022'D, (&LOOP-1), E))-%SYSFUNC(INTNX(QTR,'01JAN2022'D, (&LOOP-1), B)) + 1);
%LET QUARTER_DAY_START = %SYSFUNC(DEQUOTE(" '%SYSFUNC(INTNX(MONTH, '01JAN2022'D, (&LOOP-1)*3, B), DATE11.)' "));
%LET QUARTER_DAY_END = %SYSFUNC(DEQUOTE(" '%SYSFUNC(INTNX(MONTH, '01JAN2022'D, ((&LOOP-1)*3)+ 2, E), DATE11.)' "));
%PUT &=DAYS;
%PUT &=QUARTER_DAY_START;
%PUT &=QUARTER_DAY_END;

DATA WORK.MY_NEW_TABLE;
SET WORK.MY_OLD_TABLE;

%IF MARKET = XXX %THEN
%DO;
DATE = &QUARTER_DAY_START TO &QUARTER_DAY_END;
OUTPUT;
%END;
RUN;

%LET LOOP=%SYSEVALF(&LOOP+1);
%END;
%MEND;

LOG:

13 DATE = &QUARTER_DAY_START TO QUARTER_DAY_END;
__
388
202
ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: Problem with inserting DATE from a macro variable

Tom — Tue, 03 Jan 2023 23:56:04 GMT

Here is one obvious mistake:

%IF MARKET = XXX %THEN %DO;

The string MARKET is NEVER going to equal the string XXX. It cannot happen.

Re: Problem with inserting DATE from a macro variable

Tom — Tue, 03 Jan 2023 23:58:08 GMT

Here is an obvious over complication:

%LET LOOP = 1;
%DO %WHILE (&LOOP <= 4);
...
%LET LOOP=%SYSEVALF(&LOOP+1);
%END;

If you want LOOP to iterate from from 1 to 4 just say that.

%DO LOOP=1 %to 4 ;
...
%END;

Re: Problem with inserting DATE from a macro variable

Tom — Wed, 04 Jan 2023 00:10:30 GMT

Are you just trying to do this?

DATA WORK.MY_NEW_TABLE;
  SET WORK.MY_OLD_TABLE;
  if market = xxx then do qtr=1 to 4;
    do date = intnx('qtr','01jan2022'd,qtr-1,'b') to intnx('qtr','01jan2022'd,qtr-1,'e');
      OUTPUT;
    end;
  end;
RUN;

What are the variables MARKET and XXX? Why does it matter whether they are equal or not? Do you need to attach a format to the DATE variable? Is it a NEW variable or did it already exist in MY_OLD_TABLE?

Re: Problem with inserting DATE from a macro variable

Emoji — Wed, 04 Jan 2023 00:19:02 GMT

Variable Market is a variable (string) from Work.my_old_table. By using Market = XXX, I was trying to say that the when String market is XXX (in reality it is something like 'market_data', the rest of the code should be executed.

I ran the code that you shared and got the following error:

49 date = intnx('qtr','01jan2022'd,qtr-1,'b') to intnx('qtr','01jan2022'd,qtr-1,'e');
__
22
2 The SAS System 06:13 Tuesday, December 13, 2022

202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: Problem with inserting DATE from a macro variable

Kurt_Bremser — Wed, 04 Jan 2023 00:23:26 GMT

ALWAYS post complete logs of a step. Without context, one line and an error message is mostly useless.

Re: Problem with inserting DATE from a macro variable

Emoji — Wed, 04 Jan 2023 00:26:08 GMT

Thanks for the reply. Let's forget about this equality constraints as I wrote to simplify my code for representation here. In my actual code, variable Market has different values.

Re: Problem with inserting DATE from a macro variable

Kurt_Bremser — Wed, 04 Jan 2023 00:26:30 GMT

@Emoji wrote:

if Market = 'MARKET_2022' then do Date = '01-JAN-2022'd to '31-MAR-2022'd;

This is a DATA STEP IF statement. What you posted previously was a MACRO %IF statement.

Re: Problem with inserting DATE from a macro variable

Kurt_Bremser — Wed, 04 Jan 2023 00:30:13 GMT

@Emoji wrote:
Thanks for the reply. Let's forget about this equality constraints as I wrote to simplify my code for representation here. In my actual code, variable Market has different values.

But the macro %IF does not work with data step variables, it only "sees" text. It creates code before the data step is even compiled.

Re: Problem with inserting DATE from a macro variable

Patrick — Wed, 04 Jan 2023 02:31:53 GMT

I believe you get the error because of a missing DO statement.

If I understand right what you're after then both of below two options should work.

Option 1:

data test;
  format date date9.;
  do qtr=1 to 4;
    do date = intnx('qtr','01jan2022'd,qtr-1,'b') to intnx('qtr','01jan2022'd,qtr-1,'e');
      output;
    end;
  end;
run;

Option 2:

data test;
  format date date9.;
  do date = '01jan2022'd to '31dec2022'd;
    qtr=qtr(date);
    output;
  end;
run;

Re: Problem with inserting DATE from a macro variable

Tom — Wed, 04 Jan 2023 02:53:13 GMT

@Emoji wrote:

Variable Market is a variable (string) from Work.my_old_table. By using Market = XXX, I was trying to say that the when String market is XXX (in reality it is something like 'market_data', the rest of the code should be executed.

I ran the code that you shared and got the following error:

49 date = intnx('qtr','01jan2022'd,qtr-1,'b') to intnx('qtr','01jan2022'd,qtr-1,'e');
__
22
2 The SAS System 06:13 Tuesday, December 13, 2022

202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

To test a variable's value you need to use SAS code, not MACRO code.

There is no way that the code I posted would produce that message.

It looks like you removed the DO keyword from the DO loop.

The syntax for an iterative DO loop is

do <varname> = <initial value> [to <final_value>] [ by <increment> ] ;

Re: Problem with inserting DATE from a macro variable

PaigeMiller — Wed, 04 Jan 2023 16:01:08 GMT

You don't state what this code is supposed to be doing, and it is always helpful to state what the code is supposed to be doing.

It looks like you are trying to compute the number of days in a quarter by using a loop, and it was already explained in your earlier thread that loops are not needed to do this.

So that's my guess anyway. Regardless, tell us the purpose of this code, tell us what it is supposed to be doing. Whatever the purpose, you have adopted a very difficult approach to get this done, and if we only knew what you were doing, I'm sure many people could suggest simpler and faster ways to do what you want.

Re: Problem with inserting DATE from a macro variable

Reeza — Wed, 04 Jan 2023 16:37:49 GMT

I like @Patrick's second solution.

data my_new_table;
  set my_old_table;
  format date date9.;
  if market = "xxxx" then do date = '01jan2022'd to '31dec2022'd;
    qtr=qtr(date);
    output;
  end;
run;

You say you want to calculate the number of days in each quarter but don't show how it's used.

If after all this you still do not have a solution that works, I would suggest taking a step back as this is definitely an xy problem.

Rather than posting your non working code, please show us a small sample of your input data and what you want as output instead.