Hello,
Here is a simplified example of the probleme i meet :
data have;
infile cards dlm=',';
input a $ b$;
cards;
+10%, AAA
-8%, BBB
;
run;
proc sql noprint;
SELECT a, b
INTO :lst_a SEPARATED BY '¤',
:lst_b SEPARATED BY '¤'
FROM have;
quit;
%macro test;
data _NULL_;
%do i=1 %to %sysfunc(countw(&lst_a.));
put "%scan(%nrbquote(&lst_a.),&i.,'¤') - %scan(%nrbquote(&lst_b.),&i.,'¤')";
%end;
run;
%mend test;
%test;
The log indicates the following errors :
ERROR: The value ¤ is not a valid SAS name.
WARNING: Apparent invocation of macro ¤ not resolved.
ERROR: The value ¤ is not a valid SAS name.
WARNING: Apparent invocation of macro ¤ not resolved.
ERROR: The value ¤ is not a valid SAS name.
WARNING: Apparent invocation of macro ¤ not resolved.
ERROR: The value ¤ is not a valid SAS name.
WARNING: Apparent invocation of macro ¤ not resolved
If you are wondering what is the goal, in reality, my have dataset contains metadata for the automatic generation of datasets. There is a "label" column with certain values ending with the "%" character.
How can i avoid the "%" to be interpreted as a macro identifier ?
Thanks
Using single quotes around the label values will prevent the macro processor from trying to evaluate macro triggers.
If you are going to use metadata to generate code then I find it helps to name to variables in the metadata for the option they represent. Also do not blindly attach $xx formats to variables. SAS does not need them and they can cause havoc when you start combining datasets.
data meta;
infile cards dsd truncover ;
length memname name $32 label $256 length $7 format informat $41;
input memname -- informat;
cards;
Companies,name,Name of the company,$20,,
Companies,address,Address of the company,$100,,
Companier,percent,% or market,8,percent.,
;
run;
proc print; run;
filename code temp;
data _null_;
file code ;
set meta end=eof;
if _n_=1 then put 'data ' memname ';' ;
put ' attrib ' name length= @;
if not missing(format) then put format= @;
if not missing(informat) then put informat= @;
if not missing(label) then do;
label = quote(trim(label),"'");
put label=@ ;
end;
put ';' ;
if eof then put 'run;';
run;
2432 %include code / source2 ; NOTE: %INCLUDE (level 1) file CODE is file C:\Users\ABERNA~1\AppData\Local\Temp\1\SAS Temporary Files\_TD29228_AMRL20B7F00CGPP_\#LN00085. 2433 +data Companies ; 2434 + attrib name length=$20 label='Name of the company' ; 2435 + attrib address length=$100 label='Address of the company' ; 2436 + attrib percent length=8 format=percent. label='% or market' ; 2437 +run; NOTE: Variable name is uninitialized. NOTE: Variable address is uninitialized. NOTE: Variable percent is uninitialized. NOTE: The data set WORK.COMPANIES has 1 observations and 3 variables.
Sorry, sounds a bit like a broken records here, but why. Macro is not the place to be doing data processing. You have basically created mess of code to effectively do:
data have; length want $200; infile cards dlm=','; input a $ b$; want=catx('-',a,b); cards; +10%, AAA -8%, BBB ; run; proc print data=have; run;
I was expecting you 😉
Yes, i know what macros are and i try not to overuse them.
Here is more precisely what i want to do :
/* Metadata describing datasets to be generated automatically */
data meta;
infile cards dlm=","
length ds $8 var $10 desc $100 len fmt infmt $5;
input ds var desc len fmt infmt;
cards;
Companies,name,Name of the company,$20,$20.,$20.
Companies,adress,Adress of the company,$100,$100.,$100.
;
run;
/* Now, I want to use meta to generate the companies dataset */
data companies;
attrib /* Automatically generated formats, labels ... */
...
run;
Exporting the meta columns in macrovariable lists to generate the attrib command was the simplest way that came up to me but
if you have a non macro simple solution, i am interested.
Unless you have a proper metadata program, then a few methods spring to mind.
- Have a template dataset, this can be set with your data at run time
- Have a view
- Have a describe table output which can generate the table at run time (http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473675.htm)
- Use call execute
Those are four that spring to mind.
Using single quotes around the label values will prevent the macro processor from trying to evaluate macro triggers.
If you are going to use metadata to generate code then I find it helps to name to variables in the metadata for the option they represent. Also do not blindly attach $xx formats to variables. SAS does not need them and they can cause havoc when you start combining datasets.
data meta;
infile cards dsd truncover ;
length memname name $32 label $256 length $7 format informat $41;
input memname -- informat;
cards;
Companies,name,Name of the company,$20,,
Companies,address,Address of the company,$100,,
Companier,percent,% or market,8,percent.,
;
run;
proc print; run;
filename code temp;
data _null_;
file code ;
set meta end=eof;
if _n_=1 then put 'data ' memname ';' ;
put ' attrib ' name length= @;
if not missing(format) then put format= @;
if not missing(informat) then put informat= @;
if not missing(label) then do;
label = quote(trim(label),"'");
put label=@ ;
end;
put ';' ;
if eof then put 'run;';
run;
2432 %include code / source2 ; NOTE: %INCLUDE (level 1) file CODE is file C:\Users\ABERNA~1\AppData\Local\Temp\1\SAS Temporary Files\_TD29228_AMRL20B7F00CGPP_\#LN00085. 2433 +data Companies ; 2434 + attrib name length=$20 label='Name of the company' ; 2435 + attrib address length=$100 label='Address of the company' ; 2436 + attrib percent length=8 format=percent. label='% or market' ; 2437 +run; NOTE: Variable name is uninitialized. NOTE: Variable address is uninitialized. NOTE: Variable percent is uninitialized. NOTE: The data set WORK.COMPANIES has 1 observations and 3 variables.
Thanks a lot @RW9 and @Tom for your feedback.
Our metadata table is an import from a specification file which is not under our responsibility. This specification is subject
to frequent evolutions and the goal is to absorb those evolutions the more transparently possible.
Using call execute or generating a temporary file as you suggest should provide us a non macro solution.
Just as a word of advice, get a data specification/import document drawn up and signed off for that metadata (I know sounds daft, metadata about metadata), otherwise you will end up playing a game of what I call pin the tail on the still live donkey in the field, you may catch it unawares first and get it stuck on, but any attempt after that will take x% more effort due to a moving target. A signed off spec gives you leverage to push budgets and such like.
Thanks for the advice. I agree with you. We have well defined procedures where every evolution is materialized by a contract and no evolution is taken into account until agreed by all parties.
I think the original question:
How can I avoid the "%" to be interpreted as a macro identifier?
is good to answer.
Unfortunately, %NRBQUOTE does not prevent macro triggers from resolving (despite what one might expect from it's name). For that, you need to use %SUPERQ. I have yet to find a use for %NRBQUOTE. I think a complete package of macro quoting functions is %STR, %NRSTR, %BQUOTE, %SUPERQ.
Below works:
%macro test;
%local i ;
data _NULL_ ;
%do i=1 %to %sysfunc(countw(%superq(lst_a),¤)) ;
put "%scan(%superq(lst_a),&i.,¤) - %scan(%superq(lst_b),&i.,¤)" ;
%end ;
run ;
%mend test ;
Log is:
MPRINT(TEST): data _NULL_ ; MPRINT(TEST): put "+10% - AAA" ; MPRINT(TEST): put "-8% - BBB" ; MPRINT(TEST): run ; +10% - AAA -8% - BBB
Key changes:
As a side note, if you use pipe as a delimiter, you would not need macro quoting in this case at all, because macro names cannot start with a pipe. So below works fine too:
proc sql noprint;
SELECT a, b
INTO :lst_a SEPARATED BY '|',
:lst_b SEPARATED BY '|'
FROM have;
quit;
%macro test;
%local i ;
data _NULL_ ;
%do i=1 %to %sysfunc(countw(&lst_a,|)) ;
put "%scan(&lst_a,&i.,|) - %scan(&lst_b,&i.,|)" ;
%end ;
run ;
%mend test ;
Thanks @Quentin for these usefull additions.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.