Desktop productivity for business analysts and programmers

Filtering Not Correct With Macro Variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Filtering Not Correct With Macro Variable

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

 

 

Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 6,502

Re: Filtering Not Correct With Macro Variable

[ Edited ]

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

 

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Super User
Posts: 6,502

Re: Filtering Not Correct With Macro Variable

[ Edited ]

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

 

Occasional Contributor
Posts: 7

Re: Filtering Not Correct With Macro Variable

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

 
Super User
Super User
Posts: 6,502

Re: Filtering Not Correct With Macro Variable

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 138 views
  • 1 like
  • 2 in conversation