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.
Haven't tried, but if you could try single qoutes instead?
25076 - Resolve a macro variable within single quotation marks (sas.com)
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 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.
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 ;
@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;
@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...
@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 .
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...
@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.
or like:
like '%' !! "_&i"
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 '^'
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 ;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.