BookmarkSubscribeRSS Feed
kostek
Calcite | Level 5
Hi all

I've got a problem with following simple program:

------------------------------------------------------------------------------------------------------------

%macro convertToWhere(string,from,to);

%let return=%str();

%do i=1 %to %length(&string);

%let character=%quote(%substr(&string,&i,1));

%if("&character" = "&from") %then
%let character = &to;

%let return=&return&character;

%end;

%bquote(')&return%bquote(');

%mend;

%let list=Alfred*Alice*Barbara;

%let where=%convertToWhere(&list,*,' ');


data _null_;

set SASHELP.CLASS;
where NAME in ( &where );

run;


--------------------------------------------------------------------------------------------------------------

Macro variable "where" should return text 'Alfred' 'Alice' 'Barbara' and it actually does! but I have no Idea why I get this error:

Line generated by macro variable "WHERE".
1 'Alfred' 'Alice' 'Barbara'
-
22
-
76

Syntax error while parsing WHERE clause.



When I declere variable where manually
%let where= 'Alfred' 'Alice' 'Barbara';
data step works fine.

So it seems that the problem is in converting macro...have You any idea ??

Thanks for help.
8 REPLIES 8
Peter_C
Rhodochrosite | Level 12
try %unquote() as you use the generated string
kostek
Calcite | Level 5
thanks for reply

do You mean:

data _null_;

set SASHELP.CLASS;
where in ( %unquote(&where) );

run;


?
still not working... Message was edited by: kostek
art297
Opal | Level 21
I think it will work it you both change your single quotes to double quotes and then appy Peter's suggestion. E.g.:

%macro convertToWhere(string,from,to);

%let return=%str();

%do i=1 %to %length(&string);

%let character=%quote(%substr(&string,&i,1));

%if("&character" = "&from") %then
%let character = &to;

%let return=&return&character;

%end;

%bquote(")&return%bquote(");

%mend;

%let list=Alfred*Alice*Barbara;

%let where=%convertToWhere(&list,*," ");

data _null_;
set SASHELP.CLASS;
where NAME in ( %unquote(&where.));
run;

HTH,
Art
data_null__
Jade | Level 19
That's interesting it works on my SAS 9.1.3 for windows.

Would you care to tell us how you intend to use this. I ask because, I used to write similar code to do various tasks that I though I needed for processing list of words contained in macro variables. I was all very enjoyable until I reaslize that most of that could be more effectively using tools that SAS already provides in the data step or with procs.

You could also do away with the macro that iterates over each character LIST with a rather elaborate assignment statement.

[pre]
3066 %let where = %qsysfunc(translate(%qsysfunc(tranwrd(%superQ(LIST),%str(*),%str('
3066! ')),$QUOTE1024.),%str(%"),%str(%')));
3067 %put NOTE: %superQ(where);
NOTE: "Alfred" "Alice" "Barbara"
[/pre]


[pre]
2995 %macro convertToWhere(string,from,to);
2996 %let return=%str();
2997 %do i=1 %to %length(&string);
2998 %let character=%quote(%substr(&string,&i,1));
2999 %if("&character" = "&from") %then
3000 %let character = &to;
3001 %let return=&return&character;
3002 %end;
3003 %bquote(')&return%bquote(');
3004 %mend;
3005
3006 %let list=Alfred*Alice*Barbara;
3007 %let where=%convertToWhere(&list,*,' ');
3008 %put NOTE: %superQ(where);
NOTE: 'Alfred' 'Alice' 'Barbara'
3009
3010 data _null_;
3011 set SASHELP.CLASS;
3012 where NAME in ( %unquote(&where) );
3013 run;

NOTE: There were 3 observations read from the data set SASHELP.CLASS.
WHERE NAME in ('Alfred', 'Alice', 'Barbara');
[/pre]
kostek
Calcite | Level 5
Thanks Art - You're right, It works with double quotation 🙂

data _null_; respect 🙂 this solution looks good for me and I agree it's sometimes better to use sas tools than write own strange progs which eventually don't work properly 😉

Thanks a lot
kostek
art297
Opal | Level 21
FWIW, I was wrong! It would have worked with either single or double quotes.

Art
PatrickG
SAS Employee
For this line you'd need double quotes because the SAS Macro Facility won't look inside of single-quoted strings.

%bquote(')&return%bquote(');
chang_y_chung_hotmail_com
Obsidian | Level 7
It is well documented that the macro quoting may confuse the tokenizer. Read the last paragraph about this happening with a %bquote()'ed quotation mark here. Time to time, we may have to explicitly %unquote().

Maybe this is one of the reasons why we have this convention of naming a macro with a "q" prefix when it returns a macro quoted string. (For instance, %qTrim(), %qSubstr(), ...)

Another way to prevent this problem is just to return an unquoted string instead. For your problem, I would rather write two simple macros with more explicit names like below. HTH.



   %*-- single quote --*;


   %macro squote(str);


     %if %superq(str)= %then %return;


     %local s;


     %let s = %str(%');


     %unquote(&s%sysfunc(tranwrd(&str,&s,&s&s))&s)


   %mend  squote;


 


   %*-- comma separated quoted values --*;


   %macro csqv(list, dlm=*);


     %if %superq(list)= %then %return;


     %local i item;


     %let i = 1;


     %let item = %qscan(&list, &i);


     %do %while (&item ^=);


       %if &i > 1 %then %*;,;


       %*;%squote(&item)


       %let i = %eval(&i + 1);


       %let item = %qscan(&list, &i); 


     %end;


   %mend  csqv;


 


   %*-- check --*;


   proc print data=sashelp.class;


     where name in (%csqv(Alfred*Alice*Barbara));


   run;


   /* on lst


   Obs     Name      Sex    Age    Height    Weight


     1    Alfred      M      14     69.0      112.5


     2    Alice       F      13     56.5       84.0


     3    Barbara     F      13     65.3       98.0


   */

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1284 views
  • 0 likes
  • 6 in conversation