Problem with quoted macrovariable

[Filipvdr]

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

[Narasimha_Kulkarni33]

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

[data_null__]

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;

[Filipvdr]

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?

[data_null__]

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

[billfish]

You can use

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

and then the data step works:

data test;

value = "&value";

run;

[Filipvdr]

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.

[Tom · Accepted solution]

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

[Filipvdr]

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

[RW9]

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?