DATA Step, Macro, Functions and more

Problem with quoted macrovariable

Accepted Solution Solved
Reply
Regular Contributor
Posts: 237
Accepted Solution

Problem with quoted macrovariable

Hello all,

I'm not finding a solution for this:

I have a stored process which will write some values into a dataset

%let value = ABC","123","TEST","HELLO","WHAT;

data test;

value = "&value";

run;

ofcourse, this does not work... i guess there is a really simple solution for this?

Thanks,

Filip


Accepted Solutions
Solution
‎08-06-2015 08:41 AM
Super User
Super User
Posts: 7,077

Re: Problem with quoted macrovariable

Sounds like the value is quoted and so the double quotes internal to the value the macro variable VALUE are not being treated as double quotes.

Try something like:

where 1=1 and value in (%unquote("&value"))

View solution in original post


All Replies
New Contributor
Posts: 3

Re: Problem with quoted macrovariable

This can be solved by the use of %QUOTE and %NRQUOTE Functions in macros. Please go through the link

SAS(R) 9.2 Macro Language: Reference

Respected Advisor
Posts: 3,799

Re: Problem with quoted macrovariable

Here method using SCAN function.  There are many other ways too.

%let value = "ABC","123","TEST","HELLO","WHAT";
data test;
   length valuelist $256;
   valuelist = symget(
'VALUE');
   drop valuelist i;
   do i = 1 by 1;
      value = dequote(scan(valuelist,i,
','));
      if missing(value) then leave;
      output;
     
end;
  
stop;
  
run;
proc print;
  
run;
Regular Contributor
Posts: 237

Re: Problem with quoted macrovariable

Posted in reply to data_null__

I will explain a little bit more in detail:

A user can save his query by clicking the button, a stored process launches which will save his filter settings.

For example: he wants to filter the countries BE,NL and FR. The value which is passed to the SAVE stored process is BE","NL","FR.

I need to store this in a column, in one field.

Later, the user can use an other stored process, to retrieve his old query.

http://server/program=FILTER&countries=BE","NL",FR"

I hope this is more clear?

Respected Advisor
Posts: 3,799

Re: Problem with quoted macrovariable

%let value = "ABC","123","TEST","HELLO","WHAT";
data test;
   length valuelist $256;
   valuelist = symget(
'VALUE');
   run;
proc print;
  
run;
Contributor
Posts: 52

Re: Problem with quoted macrovariable

You can use

%let value = %nrbquote(ABC","123","TEST","HELLO","WHAT);

and then the data step works:

data test;

value = "&value";

run;

Regular Contributor
Posts: 237

Re: Problem with quoted macrovariable

thanks, i used billfish his answer.

I now save ABC","123","TEST","HELLO","WHAT in my column.

Now, i'm passing this value through an URL like this:

http://server:8080/SASStoredProcess/do?_program=test&VALUE=ABC","123","TEST","HELLO","WHAT&_debug=lo...

but it does not work... for some reason my wherestring starts and ends with double quotes but if you view the note, i have single quotes, any idea?

I put my whereclause to the log:

WHERESTRING: 1=1 and value in ("ABC","123","TEST","HELLO","WHAT") and datepart(datim) > 19941 and datepart(datim) < 20306

NOTE: There were 0 observations read from the data set WORK.TXRF_MAIN.
  WHERE (1=1) and (wafersource='REFLEXION') and (value='ABC","123","TEST","HELLO","WHAT') and (DATEPART(datim)>19941) and (DATEPART(datim)<20306);
NOTE: The data set WORK.SELECT has 0 observations and 34 variables.



Solution
‎08-06-2015 08:41 AM
Super User
Super User
Posts: 7,077

Re: Problem with quoted macrovariable

Sounds like the value is quoted and so the double quotes internal to the value the macro variable VALUE are not being treated as double quotes.

Try something like:

where 1=1 and value in (%unquote("&value"))

Regular Contributor
Posts: 237

Re: Problem with quoted macrovariable

Thanks Tom! This works! Did not know that function.

Super User
Super User
Posts: 7,997

Re: Problem with quoted macrovariable

Hi,

Well, I have not used Stored Process, so don't know how that works.  I will however point out that in my opinion macro variables are not a good storeage for lists of data.  Whenever dealing with lists I would always put them into a dataset, e.g.

data list;

     item="ABC"; output;

     item="123"; output;

run;

Then when I need to use them in a where:

where value in (select ITEM from list)

Its just far more robust, and generally less coding (especially %quote(&&&%unquote() and suchlike).  Plus, I would expect you want to do some kind of checking on the parameters given?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 576 views
  • 4 likes
  • 6 in conversation