topic Re: Problem with quoted macrovariable in SAS Programming

Problem with quoted macrovariable

Filipvdr — Wed, 05 Aug 2015 15:24:05 GMT

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

Re: Problem with quoted macrovariable

Narasimha_Kulkarni33 — Wed, 05 Aug 2015 15:32:44 GMT

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

Re: Problem with quoted macrovariable

data_null__ — Wed, 05 Aug 2015 15:40:55 GMT

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;

Re: Problem with quoted macrovariable

Filipvdr — Wed, 05 Aug 2015 15:44:22 GMT

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?

Re: Problem with quoted macrovariable

billfish — Wed, 05 Aug 2015 15:48:32 GMT

You can use

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

and then the data step works:

data test;

value = "&value";

run;

Re: Problem with quoted macrovariable

data_null__ — Wed, 05 Aug 2015 15:49:34 GMT

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

Re: Problem with quoted macrovariable

Filipvdr — Thu, 06 Aug 2015 09:29:17 GMT

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=log

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.

Re: Problem with quoted macrovariable

RW9 — Thu, 06 Aug 2015 09:55:12 GMT

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?

Re: Problem with quoted macrovariable

Tom — Thu, 06 Aug 2015 12:41:32 GMT

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

Re: Problem with quoted macrovariable

Filipvdr — Thu, 06 Aug 2015 12:50:59 GMT

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