BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ErinKSimmons
Obsidian | Level 7

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

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
Tom
Super User Tom
Super User

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

 

ErinKSimmons
Obsidian | Level 7

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

 
Tom
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1337 views
  • 2 likes
  • 2 in conversation