BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
samlac
Fluorite | Level 6

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 "&QUOTE_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. = &QUOTE_CHAR.%SCAN(&LIST., &I., " ")&QUOTE_CHAR.;
		%END;
		%ELSE %DO;
    		%LET &OUTPUT_VAR. = &&&OUTPUT_VAR..&SEPERATOR.&QUOTE_CHAR.%SCAN(&LIST., &I., " ")&QUOTE_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

samlac_0-1712343671172.png

 

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 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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".

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

8 REPLIES 8
ballardw
Super User

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.

 

 

samlac
Fluorite | Level 6

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:

samlac_0-1712347920533.png

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..

ballardw
Super User

@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...

yabwon
Onyx | Level 15

@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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Onyx | Level 15

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".

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



hashman
Ammonite | Level 13
Right on, Bart!
Tom
Super User Tom
Super User

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');

SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1482 views
  • 8 likes
  • 5 in conversation