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