warning in macro

[Satori]

I'm running this macro:

option mprint symbolgen;
%macro ex(dsin,dsout);
%let keynames=Company_name Country ISO ID Status Standardised_legal_form Type_of_entity Consolidation_code Filing_type
	NACE_primary NACE_secondary NAICS_primary NAICS_secondary SIC_primary SIC_secondary;
%do i=2008 %to 2022;
proc contents data=&dsin out=list(keep=name) noprint; run;
proc sql noprint; select nliteral(name), catx('=', nliteral(name), substr(name, 1, length(name)-5)) into
	:keep_list separated by ' ', :rename_list separated by ' ' from list where upcase(trim(name)) like "%_&i"; quit;
data brics_&i; set &dsin(keep=&keynames &keep_list); %if (&sqlobs) %then %do; rename &rename_list; %end; run;
%end;
data &dsout; set brics_2008-brics_2022; run;
%mend;

proc import datafile="/data/Export_usd.xlsx" out=usd dbms=xlsx replace;
%ex(usd,o_usd);

The code is doing what I want it to do (at least, I think it is), but there are some warnings appearing in the log about invoking macros that are not resolved. Part of the log is below (then this part is repeated for the other years: 2008 to 2022, always the same invokation not resolved) 

SYMBOLGEN:  Macro variable DSIN resolves to usd
MPRINT(EX):   proc contents data=usd out=list(keep=name) noprint;
MPRINT(EX):   run;

NOTE: The data set WORK.LIST has 885 observations and 1 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
      

MPRINT(EX):   proc sql noprint;
SYMBOLGEN:  Macro variable I resolves to 2008
WARNING: Apparent invocation of macro _2008 not resolved.
SYMBOLGEN:  Macro variable I resolves to 2008
WARNING: Apparent invocation of macro _2008 not resolved.
MPRINT(EX):   select nliteral(name), catx('=', nliteral(name), substr(name, 1, length(name)-5)) into :keep_list separated by ' ', :rename_list separated by ' ' from list where 
upcase(trim(name)) like "%_2008";
MPRINT(EX):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
      

SYMBOLGEN:  Macro variable I resolves to 2008
MPRINT(EX):   data brics_2008;
SYMBOLGEN:  Macro variable DSIN resolves to usd
SYMBOLGEN:  Macro variable KEYNAMES resolves to Company_name Country ISO ID Status Standardised_legal_form Type_of_entity Consolidation_code Filing_type  NACE_primary 
            NACE_secondary NAICS_primary NAICS_secondary SIC_primary SIC_secondary
SYMBOLGEN:  Macro variable KEEP_LIST resolves to Accounting_practice_2008 Added_value_2008 Capital_2008 Cash_equivalent_2008 Cash_flow_2008 Closing_date_2008 
            Costs_of_employees_2008 Costs_of_goods_sold_2008 Creditors_2008 Current_assets_2008 Current_liabilities_2008 Debtors_2008 Depreciation_Amortization_2008 EBITDA_2008 
            Enterprise_value_2008 Exchange_rate_2008 Export_revenue_2008 Extr_and_other_P_L_2008 Extr_and_other_exp_2008 Extr_and_other_rev_2008 Financial_P_L_2008 
            Financial_expenses_2008 Financial_revenue_2008 Fixed_assets_2008 Gross_profit_2008 Intangible_fixed_assets_2008 Interest_paid_2008 Loans_2008 Long_term_debt_2008 
            Material_costs_2008 Net_current_assets_2008 Non_current_liab_2008 Number_of_employees_2008 Number_of_months_2008 Operating_P_L_EBIT_2008 Operating_revenue_2008 
            Original_currency_2008 Original_units_2008 Other_current_assets_2008 Other_current_liabilities_2008 Other_fixed_assets_2008 Other_non_current_liab_2008 
            Other_operating_expenses_2008 Other_operating_items_2008 Other_shareholders_funds_2008 P_L_after_tax_2008 P_L_before_tax_2008 P_L_for_period_Net_income_2008 
            Provisions_2008 R_D_expenses_2008 Sales_2008 Shareholders_funds_2008 Stock_2008 Tangible_fixed_assets_2008 Taxation_2008 Total_assets_2008 Total_shareh_funds_liab_2008 
            Working_capital_2008
MPRINT(EX):   set usd(keep=Company_name Country ISO ID Status Standardised_legal_form Type_of_entity Consolidation_code Filing_type NACE_primary NACE_secondary 
NAICS_primary NAICS_secondary SIC_primary SIC_secondary Accounting_practice_2008 Added_value_2008 Capital_2008 Cash_equivalent_2008 Cash_flow_2008 Closing_date_2008 
Costs_of_employees_2008 Costs_of_goods_sold_2008 Creditors_2008 Current_assets_2008 Current_liabilities_2008 Debtors_2008 Depreciation_Amortization_2008 EBITDA_2008 
Enterprise_value_2008 Exchange_rate_2008 Export_revenue_2008 Extr_and_other_P_L_2008 Extr_and_other_exp_2008 Extr_and_other_rev_2008 Financial_P_L_2008 Financial_expenses_2008 
Financial_revenue_2008 Fixed_assets_2008 Gross_profit_2008 Intangible_fixed_assets_2008 Interest_paid_2008 Loans_2008 Long_term_debt_2008 Material_costs_2008 
Net_current_assets_2008 Non_current_liab_2008 Number_of_employees_2008 Number_of_months_2008 Operating_P_L_EBIT_2008 Operating_revenue_2008 Original_currency_2008 
Original_units_2008 Other_current_assets_2008 Other_current_liabilities_2008 Other_fixed_assets_2008 Other_non_current_liab_2008 Other_operating_expenses_2008 
Other_operating_items_2008 Other_shareholders_funds_2008 P_L_after_tax_2008 P_L_before_tax_2008 P_L_for_period_Net_income_2008 Provisions_2008 R_D_expenses_2008 Sales_2008 
Shareholders_funds_2008 Stock_2008 Tangible_fixed_assets_2008 Taxation_2008 Total_assets_2008 Total_shareh_funds_liab_2008 Working_capital_2008);
SYMBOLGEN:  Macro variable SQLOBS resolves to 58
SYMBOLGEN:  Macro variable RENAME_LIST resolves to Accounting_practice_2008=Accounting_practice Added_value_2008=Added_value Capital_2008=Capital 
            Cash_equivalent_2008=Cash_equivalent Cash_flow_2008=Cash_flow Closing_date_2008=Closing_date Costs_of_employees_2008=Costs_of_employees 
            Costs_of_goods_sold_2008=Costs_of_goods_sold Creditors_2008=Creditors Current_assets_2008=Current_assets Current_liabilities_2008=Current_liabilities 
            Debtors_2008=Debtors Depreciation_Amortization_2008=Depreciation_Amortization EBITDA_2008=EBITDA Enterprise_value_2008=Enterprise_value 
            Exchange_rate_2008=Exchange_rate Export_revenue_2008=Export_revenue Extr_and_other_P_L_2008=Extr_and_other_P_L Extr_and_other_exp_2008=Extr_and_other_exp 
            Extr_and_other_rev_2008=Extr_and_other_rev Financial_P_L_2008=Financial_P_L Financial_expenses_2008=Financial_expenses Financial_revenue_2008=Financial_revenue 
            Fixed_assets_2008=Fixed_assets Gross_profit_2008=Gross_profit Intangible_fixed_assets_2008=Intangible_fixed_assets Interest_paid_2008=Interest_paid Loans_2008=Loans 
            Long_term_debt_2008=Long_term_debt Material_costs_2008=Material_costs Net_current_assets_2008=Net_current_assets Non_current_liab_2008=Non_current_liab 
            Number_of_employees_2008=Number_of_employees Number_of_months_2008=Number_of_months Operating_P_L_EBIT_2008=Operating_P_L_EBIT Operating_revenue_2008=Operating_revenue 
            Original_currency_2008=Original_currency Original_units_2008=Original_units Other_current_assets_2008=Other_current_assets 
            Other_current_liabilities_2008=Other_current_liabilities Other_fixed_assets_2008=Other_fixed_assets Other_non_current_liab_2008=Other_non_current_liab 
            Other_operating_expenses_2008=Other_operating_expenses Other_operating_items_2008=Other_operating_items Other_shareholders_funds_2008=Other_shareholders_funds 
            P_L_after_tax_2008=P_L_after_tax P_L_before_tax_2008=P_L_before_tax P_L_for_period_Net_income_2008=P_L_for_period_Net_income Provisions_2008=Provisions 
            R_D_expenses_2008=R_D_expenses Sales_2008=Sales Shareholders_funds_2008=Shareholders_funds Stock_2008=Stock Tangible_fixed_assets_2008=Tangible_fixed_assets 
            Taxation_2008=Taxation Total_assets_2008=Total_assets Total_shareh_funds_liab_2008=Total_shareh_funds_liab Working_capital_2008=Working_capital
MPRINT(EX):   rename Accounting_practice_2008=Accounting_practice Added_value_2008=Added_value Capital_2008=Capital Cash_equivalent_2008=Cash_equivalent Cash_flow_2008=Cash_flow 
Closing_date_2008=Closing_date Costs_of_employees_2008=Costs_of_employees Costs_of_goods_sold_2008=Costs_of_goods_sold Creditors_2008=Creditors Current_assets_2008=Current_assets 
Current_liabilities_2008=Current_liabilities Debtors_2008=Debtors Depreciation_Amortization_2008=Depreciation_Amortization EBITDA_2008=EBITDA 
Enterprise_value_2008=Enterprise_value Exchange_rate_2008=Exchange_rate Export_revenue_2008=Export_revenue Extr_and_other_P_L_2008=Extr_and_other_P_L 
Extr_and_other_exp_2008=Extr_and_other_exp Extr_and_other_rev_2008=Extr_and_other_rev Financial_P_L_2008=Financial_P_L Financial_expenses_2008=Financial_expenses 
Financial_revenue_2008=Financial_revenue Fixed_assets_2008=Fixed_assets Gross_profit_2008=Gross_profit Intangible_fixed_assets_2008=Intangible_fixed_assets 
Interest_paid_2008=Interest_paid Loans_2008=Loans Long_term_debt_2008=Long_term_debt Material_costs_2008=Material_costs Net_current_assets_2008=Net_current_assets 
Non_current_liab_2008=Non_current_liab Number_of_employees_2008=Number_of_employees Number_of_months_2008=Number_of_months Operating_P_L_EBIT_2008=Operating_P_L_EBIT 
Operating_revenue_2008=Operating_revenue Original_currency_2008=Original_currency Original_units_2008=Original_units Other_current_assets_2008=Other_current_assets 
Other_current_liabilities_2008=Other_current_liabilities Other_fixed_assets_2008=Other_fixed_assets Other_non_current_liab_2008=Other_non_current_liab 
Other_operating_expenses_2008=Other_operating_expenses Other_operating_items_2008=Other_operating_items Other_shareholders_funds_2008=Other_shareholders_funds 
P_L_after_tax_2008=P_L_after_tax P_L_before_tax_2008=P_L_before_tax P_L_for_period_Net_income_2008=P_L_for_period_Net_income Provisions_2008=Provisions 
R_D_expenses_2008=R_D_expenses Sales_2008=Sales Shareholders_funds_2008=Shareholders_funds Stock_2008=Stock Tangible_fixed_assets_2008=Tangible_fixed_assets Taxation_2008=Taxation 
Total_assets_2008=Total_assets Total_shareh_funds_liab_2008=Total_shareh_funds_liab Working_capital_2008=Working_capital;
MPRINT(EX):   run;

NOTE: There were 1418 observations read from the data set WORK.USD.
NOTE: The data set WORK.BRICS_2008 has 1418 observations and 73 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Can anyone explain to me what does this mean, and how can I change the code to remove it if possible.

[LinusH]

Haven't tried, but if you could try single qoutes instead?

25076 - Resolve a macro variable within single quotation marks (sas.com)

[Quentin]

With your code:

where upcase(trim(name)) like "%_&i"

SAS will see the percent sign as a macro token, and try to execute the macro named _2008.  Since it doesn't exist, it throws the warning.

I haven't tested, but you could try adding macro quoting, e.g.:

where name like "%nrstr(%%)_&i"

If the quoting gets hard, you could also change the syntax to avoid the use of the % sign, maybe something like:

where scan(name, -1, '_') = "&i"

[Ksharp · Accepted solution]

like "%_&i";
---->
like "%%_&i";

[Quentin]

---

@Ksharp wrote:
like "%_&i";
---->
like "%%_&i";

---

Thanks @Ksharp !  I'm confused, why does that work without %nrstr()?  I would have thought the first % would be seen as a wild card, but I expected the second % to be seen as macro trigger.

I guess it's back to macro school for me...

1    data want;
2      x="%%A" ;
3      put x= ;
4    run ;

x=%%A
NOTE: The data set WORK.WANT has 1 observations and 1 variables.

[Quentin]

I tested some more, and became more surprised. 😀

It looks like %%Foo will only avoid triggering the macro processor if it is inside double quotes or parentheses.  Which actually feels bug-ish to me.  I think all of the below tests should trigger the macro processor:

%put %%Foo ;   *Does trigger macro processor ;

%put "%%Foo" ; *Does NOT trigger macro processor ;

data want;
  x="%%Foo" ;  *Does NOT trigger macro processor ;
  put x= ;
run ;

%put %length(%%foo) ;  *Does NOT trigger macro processor ;

[Ksharp]

@Quentin

"I would have thought the first % would be seen as a wild card,
but I expected the second % to be seen as macro trigger."
My thought is different, see the third example:

data have;
 set sashelp.cars(obs=100);
 keep Model;
run;

/*First one. no problem .
Did not triger the macro processor .
*/
proc sql;
select * from have 
 where Model like "%auto";
quit;

/*Second one. Have Warning Info:
WARNING: Apparent invocation of macro AUTO not resolved.
*/
%let x=auto;
proc sql;
select * from have 
 where Model like "%&x.";
quit;

/*Third one. no problem.
My thought is double % would resolve to single % in double quote and &x. resolve to "auto"

"%%&x."
-->
"%auto"

as you pointed out "will only avoid triggering the macro processor if it is inside double quotes".
*/
%let x=auto;
proc sql;
select * from have 
 where Model like "%%&x.";
quit;

[Quentin]

@Ksharp I'm confused, your first example DOES trigger the macro processor, as it should:

9    proc sql;
10   select * from have
11    where Model like "%auto";
WARNING: Apparent invocation of macro AUTO not resolved.
12   quit;

The whole && resolves to & is true for ampersands, but that is not (as far as I know) true for percent signs.  For example if you run:

%let x=auto;
proc sql;
select "%%&x." from sashelp.class(obs=1);
quit;

The results  is  %%auto .  So it's not that two ampersands resolved to one, I think the words scanner just didn't trigger the macro processor (even though it should have).  So I think it's a tokenization bug.

It 'works' for the LIKE operator in this case because to the LIKE operator, %% is the same as %.

I'm going to submit it to tech support, just because I'm curious not because they're likely to change the behavior.  I'll post a new thread if I get an interesting response.

I need to start keeping a folder of interesting macro tokenization issues, because I'm getting old enough to forget the ones I've seen before...

[Ksharp]

@Quentin
"your first example DOES trigger the macro processor, as it should:"
Sorry. You are right. I did not check my log exactly .

"but that is not (as far as I know) true for percent signs."
Maybe you are right. and I encourage you to submit "tokenization bug" to sas support and find the answer.

And I also find some interesting thing. If the number of % was EVEN and I would not get WARNING, but If the number of % was ODD and get WARNING. Check the following example:


data have;
set sashelp.cars(obs=100);
keep Model;
run;


/*get WARNING*/
%let x=auto;
proc sql;
select * from have
where Model like "%%%&x.";
quit;

/*no WARNING*/
%let x=auto;
proc sql;
select * from have
where Model like "%%%%&x.";
quit;


So I still insist my opinion "double % would resolve to single % ".

a.k.a  double(even) % would not trigger the macro processor, single(odd) % would trigger .

[Quentin]

Thanks @Ksharp . 

I had also noticed that the issue only happens with an even number of percent signs.  It also happens with strings inside of parentheses, (%%foo), and also an ampersand followed by percent sign, "&%foo". 

I opened a track with tech support. To avoid hijacking this thread further, I opened a new thread: https://communities.sas.com/t5/SAS-Programming/Macro-language-tokenization-bug-quot-foo-quot-will-no...

[Tom]

---

@Ksharp wrote:
like "%_&i";
---->
like "%%_&i";

---

That might eliminate the warning, but it does not fix the logical error in the LIKE condition since it still leaves the _ to work as a single character wildcard.

[Ksharp]

Tom,
That is another story .

[yabwon]

or like:

like '%' !! "_&i"

[Tom]

What are you trying to detect with this WHERE condition?

where upcase(trim(name)) like "%_&i"

% is a metacharacter to the LIKE operator, it will match zero or more characters. The _ character is also a metacharacter for the LIKE operator, it will match any single character.

So do you want to find names that end with 2008 and have one or more other characters before that?

Of do you want to find names that end with underscore followed by 2008?

If you want to test for that use an ESCAPE character before the underscore.  

where upcase(trim(name)) like "%^_&i" escape '^'

Note that since &I can only contain digits the UPCASE() is not doing anything useful.

Note that since SAS string comparisons ignore trailing spaces the TRIM() is not doing anything useful.

So the end result could be:

where name like "%^_&i" escape '^'

[ballardw]

I doubt that you really need to run Proc Contents 14 times on the same data set. I would move that out of the loop.

If instead of relying on a very fragile and prone to many problems PROC IMPORT from a wide format like I assume your spreadsheet has you 1) save the spreadsheet to CSV and 2) write a proper data step to read the CSV you will prevent a likely number of issues with this attempt to fix.

One thing, just because the columns from the spread sheet have the same name stems does not mean that when Proc Import converts Columnx_2008, ColumnX_2009, ColumnX_2010 ... ColumnX_2022 that the corresponding SAS variables will even be of the same type. If any of those are supposed to be character it is extremely likely that the lengths of the variables would vary and you will get the proverbially frequent from spreadsheet imports "variable x has been defined with multiple lengths"

Note that by proper data step that can read multiple sets of values from one row into multiple observations.

Plus YOU control the variables names and don't have to put up with name literals at all.

Consider the following. The Example1 data set is a reduced version of what I imagine your spreadsheet looks like.  I have one variable that is in common to all "years" of the data. I suspect that is what your Keynames represent.  It reads the data into a single row for each id like I suspect your Proc import generates.

The second is an example of reading each row into multiple observations. Note that one of the things that your split data and append misses is a link back to the YEAR that the observations come from so is a tad fragile (though you could parse the Brics_ data set names to recover the information with the INDSNAME option.

data example1;
   input id $
         var1_2008 var2_2008 $ var3_2008
         var1_2009 var2_2009 $ var3_2009
         var1_2010 var2_2010 $ var3_2010
   ;
datalines;
xxx 1 a 11 2 b 22 3 c 33
yyy 11 aa 111 22 bb 222 33 cc 333
zzz 111 aaa 1111 222 bbb 2222 333 ccc 3333
;

data example2;
   input id $ @;
   do year=2008 to 2010;
      input var1 var2 $ var3 @;
      output;
   end;
   input;
datalines;
xxx 1 a 11 2 b 22 3 c 33
yyy 11 aa 111 22 bb 222 33 cc 333
zzz 111 aaa 1111 222 bbb 2222 333 ccc 3333
;