single quoting values using sql to create macro variable

1
votes

I am using a macro in SAS to loop over the data tables in a specific library. I put the metadata information in a data null step and make a comparison with a where statement and my macro variable.

My SQL step looks like:

proc sql;
  select quote(trim(code)) into :procedures separated by ', ' from procedures;
quit;

Some values of code contain values like "45.10" and "G0102", so cannot be coerced to numeric. The macro contains the line:

%macro filter_codes(indata, outdata);
  data &outdata;
     set &indata(where = (code in (&procedures)));
  run;
%mend;

but the decimal values create an issue when double-quoted using the "quote" function.

Can I separate values with single quotes?

EDIT: The issue was caused by the fact that the filter_codes macro was run within a call execute step (over a range of datasets) and double quotes resolved in macro variables inside of double quotes would end the call execute.

4
sassas-macro
There's nothing explicitly wrong with your code, unless your values contain the quotation marks themselves. There's certainly nothing different in how SAS uses ' from " except in macro variable resolution, which doesn't seem relevant here from your question. A simple test on my side proved it to work fine, using height from sashelp.class. Can you post some example data that shows this not working? – Joe
You're correct, the reason why this caused an error was that the quoted text was resolved within a call execute command in a data null step. I've modified the question. – AdamO

4 Answers

6
votes

Try this:

proc sql;
  select catt("'", code, "'") into :procedures separated by ', ' from procedures;
quit;

Also fix the where option in set statement:

set &indata(where=(code in (&procedures)));
2
votes

How about in one step using SQL?

%macro filter_codes(indata, outdata);
  proc sql ;
    create table &OUTDATA as
    select * from &INDATA
    where code in(select distinct code from procedures) ;
  quit ;
%mend;

No need to worry about quoting then.

1
votes

In 9.3 you have a second argument, enabling you to use single quotes in the quote() function.

proc sql noprint;
select quote(trim(code),"'") into :procedures separated by ', ' from procedures;

However.. Am not a fan of this function as it pads empty character variables with a single space (see below). 

data _null_;
  x=''; /* empty */
  y=quote(x,"'");
  put y=;  /* gives y=' ' */
run;

Unfortunately it's the only 'in built' way (other than a proc export or suchlike) to get reliable quoting applied (eg quoting the quotes).

0
votes

Fix the issue where you reference the variable. Sounds like you were trying to use something like

call execute("set &indata(where=(code in (&procedures)))") ;

First there is no need to use double quotes in the CALL EXECUTE string. Push the &procedures onto the command stack unresolved.

call execute('set &indata(where=(code in (&procedures)))') ;

If you did want to resolve the macro variable then look at using %sysfunc(quote()).

put %sysfunc(quote(The procedure list is &procedures));