Hello,
I know this one is a tricky topic..
I got a macro that can output a macro variable that contain single quotes:
%MACRO TRANSFORM_LIST_TO_QUOTED_LIST(LIST, OUTPUT_VAR, SEPERATOR=, QUOTE_CHAR=); %IF ""E_CHAR." = "" %THEN %DO; %LET QUOTE_CHAR = %NRSTR(%"); /* " closing quote for visual only */ %END; %IF "&SEPERATOR." = "" %THEN %DO; %LET SEPERATOR =%NRSTR( ); *default sep is a space ; %END; * structure the list as a list of column to keep into an SQL. ; %INIT_VAR_AS_GLOBAL(&OUTPUT_VAR.); * loop on each element of the list; %DO I=1 %TO %SYSFUNC(COUNTW("&LIST."," ",)); %IF &I.=1 %THEN %DO; %LET &OUTPUT_VAR. = "E_CHAR.%SCAN(&LIST., &I., " ")"E_CHAR.; %END; %ELSE %DO; %LET &OUTPUT_VAR. = &&&OUTPUT_VAR..&SEPERATOR."E_CHAR.%SCAN(&LIST., &I., " ")"E_CHAR.; %END; %END; %MEND TRANSFORM_LIST_TO_QUOTED_LIST;
The macro is working fine.. and if I call it like this:
%LET COLUMNS_TO_KEEP_B = Name Sex Age;
%TRANSFORM_LIST_TO_QUOTED_LIST(LIST=&COLUMNS_TO_KEEP_B., OUTPUT_VAR=HASH_COLUMNS_TO_KEEP_B, SEPERATOR=%NRSTR(,), QUOTE_CHAR=%NRSTR(%'))
the %PUT &HASH_COLUMNS_TO_KEEP_B.; function gives me 'Name', 'Sex', 'Age'
but in the global variable table the results looks really different
if I try to use it into my hash table :
RC = H_MERGE.DEFINEDATA(&HASH_COLUMNS_TO_KEEP_B.);
i'm getting this error:
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 386-185: Expecting an arithmetic expression.
ERROR 76-322: Syntax error, statement will be ignored.
If i manaully put the list like this the macro run fine.
RC = H_MERGE.DEFINEDATA('Name', 'Sex', 'Age');
So it's really seem to be an issue with the macro that dynamically genrerate the string..
Any clue How I could solve this issue?
Best regards,
Samuel
1) Read this article it really explains everything about macro quoting: "Secrets of Macro Quoting Functions – How and Why" by Susan O’Connor, link: https://stats.oarc.ucla.edu/wp-content/uploads/2016/02/bt185.pdf
2) macrovariable created by your macro contains special macro quoting symbols, they are "invisible" but are there and sometimes mess around with 4GL code. To avoid errors you have to "unmask" it with %unquote() macro function
RC = H_MERGE.DEFINEDATA(%unquote(&HASH_COLUMNS_TO_KEEP_B.));
3) your hash table can be defined simpler without commas and quotes.
Both defineData and defineKey allows to be executed multiple times to build a hash table. Code like:
declare hash H();
H.defineKey("K1", "K2");
H.defineData("D1", "D2", "D3");
H.defineDone();
is equivalent to:
declare hash H();
H.defineKey("K1");
H.defineKey("K2");
H.defineData("D1");
H.defineData("D2");
H.defineData("D3");
H.defineDone();
and that one can be "automated" like {EDIT, small update}:
%let keys= K1 K2;
%let data= D1 D2 D3;
data _null_;
declare hash H();
keys=symget('keys');
data=symget('data');
do i = 1 to countw(keys);
H.defineKey(scan(keys,i," "));
end;
do i = 1 to countw(data);
H.defineData(scan(data,i," "));
end;
H.defineDone();
retain &keys. 1 &data. 2;
h.add();
h.output(dataset:"A");
run;
so neither quotes nor commas are necessary.
All the best
Bart
P.S. My "golden rule" of macro programming says: "If you can do it (aka get exactly the same effect) with macroprogramming or without macroprogramming, do it without".
It may help to run your macro with the setting OPTIONS MPRINT; so we can see the Code generated by the macro with the error message in better context. Showing an error message without the code is pretty frustrating. So share the log including the created code and all the note, warning and error messages.
sure thing, but in this case the Error log is not really talkative!
This is the macro that is ran that is calling the things mentionned above.
DATA &OUT_TABLE_NAME.; * this is to avoid having to declare the LENGTH of every columns; IF 0 THEN SET &TABLE_A. (KEEP=&COLUMNS_TO_KEEP_A.) &TABLE_B. (KEEP=&COLUMNS_TO_KEEP_B.); DECLARE HASH H_MERGE(DATASET:"&TABLE_B." /*, MULTIDATA: "Y"*/); /*if MULTIDATA is not Y then the hash will drop any key that i there more than once */ RC = H_MERGE.DEFINEKEY('Name'); RC = H_MERGE.DEFINEDATA(&HASH_COLUMNS_TO_KEEP_B.); RC = H_MERGE.DEFINEDONE(); DO WHILE(NOT EOF); SET &TABLE_A. (KEEP=&COLUMNS_TO_KEEP_A.) END=EOF; * assign missing for columns we want to merge if there is no match; %IF %STR(&COLUMNS_TO_KEEP_B.) NE %STR() %THEN %DO; CALL MISSING(&MISSING_COLUMNS_TO_KEEP_B.); %END; RC = H_MERGE.FIND(); IF RC = 0 THEN OUTPUT; END; DROP RC; STOP; RUN;
and this is the error log:
as mentionned above you can see that the 'Name','Sex','Age' appear as normal text. if i modified that single step to write then manully the same way its works perfectly fine..
@yabwon did a much better job of explaining than I would have.
Note that one of those diagnostic messages that appear next to no actual code might be considered the symptom of one of those "hidden" delimiters. Or sometimes something that creeps into our code files when gremlins decide to pay a visit...
@ballardw thanks for kind words 🙂
I was able to explain it only thanks to lecture of Susan's article (which btw. is in my top 3 favour sas papers)
And hash tables explanation thanks to @hashman and @DonH 's book 🙂
Bart
1) Read this article it really explains everything about macro quoting: "Secrets of Macro Quoting Functions – How and Why" by Susan O’Connor, link: https://stats.oarc.ucla.edu/wp-content/uploads/2016/02/bt185.pdf
2) macrovariable created by your macro contains special macro quoting symbols, they are "invisible" but are there and sometimes mess around with 4GL code. To avoid errors you have to "unmask" it with %unquote() macro function
RC = H_MERGE.DEFINEDATA(%unquote(&HASH_COLUMNS_TO_KEEP_B.));
3) your hash table can be defined simpler without commas and quotes.
Both defineData and defineKey allows to be executed multiple times to build a hash table. Code like:
declare hash H();
H.defineKey("K1", "K2");
H.defineData("D1", "D2", "D3");
H.defineDone();
is equivalent to:
declare hash H();
H.defineKey("K1");
H.defineKey("K2");
H.defineData("D1");
H.defineData("D2");
H.defineData("D3");
H.defineDone();
and that one can be "automated" like {EDIT, small update}:
%let keys= K1 K2;
%let data= D1 D2 D3;
data _null_;
declare hash H();
keys=symget('keys');
data=symget('data');
do i = 1 to countw(keys);
H.defineKey(scan(keys,i," "));
end;
do i = 1 to countw(data);
H.defineData(scan(data,i," "));
end;
H.defineDone();
retain &keys. 1 &data. 2;
h.add();
h.output(dataset:"A");
run;
so neither quotes nor commas are necessary.
All the best
Bart
P.S. My "golden rule" of macro programming says: "If you can do it (aka get exactly the same effect) with macroprogramming or without macroprogramming, do it without".
And if you are lazy and don't want list all variables ""one-by-one" you can even make it with variables lists, e.g., k2--k5 or d7--d12
Code:
data have;
array k[7] (1:7);
array D[17] (1:17);
run;
data _null_;
if 0 then set have;
array keys[*] k2--k5; /* only part of variables selected */
array data[*] d7--d12; /* also not all selected */
declare hash H();
do i = 1 to dim(keys);
H.defineKey(vname(keys[i]));
end;
do i = 1 to dim(data);
H.defineData(vname(data[i]));
end;
H.defineDone();
set have;
array allData[*] d:;
do i = 1 to dim(allData);
allData[i] = 1000*allData[i];
end;
h.add();
h.output(dataset:"A");
stop;
run;
Bart
In general if you want to quote something use the QUOTE() function.
If you want to remove macro quoting then use the %UNQUOTE() function.
Here is a similar macro based on something Tom Hoffman created 30 years ago:
https://github.com/sasutils/macros/blob/master/qlist.sas
1 %let HASH_COLUMNS_TO_KEEP_B=%qlist(Name Sex Age,paren=0); 2 %put &=HASH_COLUMNS_TO_KEEP_B; HASH_COLUMNS_TO_KEEP_B='Name','Sex','Age'
So in your actual SAS code you won't need to use the PAREN option since the syntax needs ( ) around the list. So this statement
RC = H_MERGE.DEFINEDATA%qlist(&COLUMNS_TO_KEEP_B);
will be converted to this statement
RC = H_MERGE.DEFINEDATA('Name','Sex','Age');
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.