topic Re: Filtering Not Correct With Macro Variable in SAS Enterprise Guide

Filtering Not Correct With Macro Variable

ErinKSimmons — Tue, 08 Aug 2017 15:43:13 GMT

I have a macro variable defined to filter based on the current year. And when I use the macro variable to filter the year, it does not pull the information (essentially pulls nothing becuase the filter is not seeing any matches). However if I type in the year in filter it works. I have included both sets of code and the column properties of the table to be filtered (point_acct) and the table where the output of what the values of the macro variables are set to. Note, that all the other macro variables you will see defined filter properly.

Note there is some intervening code to establish the oracle passthrough and tables, but that wasn't germaine to this question and has been left out.

Code without macro variable:

execute(create table afc_nbr_join as
select i.inv_id, i.inv_type_cd, i.repair_cd, i.commod_cd, i.fsc_cd, i.niin, i.noun_nm, i.unit_price_amt, p.afc_nbr
from inv i, point_acct p
where i.commod_cd=p.commod_cd
and i.inv_stat_cd='A'
and p.approp_fy_nbr = '2017'
and p.commod_cd not in ('1','2','3')
union
select i.inv_id, i.inv_type_cd, i.repair_cd, i.commod_cd, i.fsc_cd, i.niin, i.noun_nm, i.unit_price_amt, p.afc_nbr
from inv i, point_acct p
where i.commod_cd=p.commod_cd
and i.inv_stat_cd='A'
and p.commod_cd in ('1','2','3')) by oracle;
disconnect from oracle;
quit;

Code with Macro Variables:

DATA _null_;
YP2DB = INTNX('year',today(),-2,'B');
YP5DB = INTNX('year',today(),-5,'B');
CALL SYMPUT('oracle_2YearDate',strip(put(YP2DB,date11.)));
CALL SYMPUT('oracle_5YearDate',strip(put(YP5DB,date11.)));
CALL SYMPUTX ('TWOYEARSPRIOR',strip((PUT(YP2DB,year4.))));
CALL SYMPUTX ('FIVEYEARSPRIOR',strip(PUT(YP5DB,year4.)));
CALL SYMPUTX ('CURRENTYEAR',strip(PUT(today(),year4.)));
RUN;

execute(create table afc_nbr_join as
select i.inv_id, i.inv_type_cd, i.repair_cd, i.commod_cd, i.fsc_cd, i.niin, i.noun_nm, i.unit_price_amt, p.afc_nbr
from inv i, point_acct p
where i.commod_cd=p.commod_cd
and i.inv_stat_cd='A'
and p.approp_fy_nbr = %str(%') &currentyear %str(%')
and p.commod_cd not in ('1','2','3')
union
select i.inv_id, i.inv_type_cd, i.repair_cd, i.commod_cd, i.fsc_cd, i.niin, i.noun_nm, i.unit_price_amt, p.afc_nbr
from inv i, point_acct p
where i.commod_cd=p.commod_cd
and i.inv_stat_cd='A'
and p.commod_cd in ('1','2','3')) by oracle;
disconnect from oracle;
quit;

Screen shots attached as word document.

Thanks,

Erin

Re: Filtering Not Correct With Macro Variable

Tom — Tue, 08 Aug 2017 15:59:16 GMT

Looks like you have added spaces inside the quotes.

        and p.approp_fy_nbr = %str(%') &currentyear %str(%')

But perhaps the macro quoting added by using %STR() is also confusing SAS? Try removing it also.

        and p.approp_fy_nbr = %unquote(%str(%')&currentyear.%str(%'))

Why not just add the single quotes into the macro variable?

 CALL SYMPUTX ('CURRENTYEAR',quote(PUT(today(),year4.),"'"));
...
        and p.approp_fy_nbr = &currentyear

Re: Filtering Not Correct With Macro Variable

ErinKSimmons — Tue, 08 Aug 2017 19:24:22 GMT

Tom-

Thank you.

For the record, the first option with the %unquote worked, but the second with putting the quotes in the symputx did not. (I also tried running it without the %str() altogether and that also didn't work, so I am not sure why but doing the Oracle passthrough in this case needed both the %unquote(%str(%') &currentyear %str(%'))).

-Erin

Re: Filtering Not Correct With Macro Variable

Tom — Tue, 08 Aug 2017 19:35:08 GMT

Adding extra macro quoting into generated code can cause havoc with SAS's ability to properly parse the generated commands.

Adding single quotes into the macro variable should work fine for the code you posted. Make sure not to add more quotes in the SQL query. You can easily test by just hard coding the macro variable. If it works that way then the issue is with the CALL SYMPUTX() syntax.

%let currentyear='2017' ;

But perhaps your code was also using that macro variable for other things where the quotes would cause trouble? Like as part of filename or variable name.

I have found that when working the pass-through code generation it is useful to have a simple %SQUOTE() macro you can use to add single quotes. Check out this one: https://github.com/sasutils/macros/blob/master/squote.sas

%macro squote(value);
%if %sysevalf(&sysver < 9.3) %then
%unquote(%str(%')%qsysfunc(tranwrd(%superq(value),%str(%'),''))%str(%'))
;
%else %sysfunc(quote(%superq(value),%str(%'))) ;
%mend squote;