single quoting values using sql to create macro variable

Question

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.

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
– Joe, Commented Aug 20, 2013 at 13:50
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
– AdamO, Commented Aug 20, 2013 at 18:44

vasja · Accepted Answer · 2013-08-20 07:22:25Z

6

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

answered Aug 20, 2013 at 7:22

vasja

4,79217 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AdamO Over a year ago

The solution is rather obvious, though not as elegant as a "quote" command. I did effectively the same thing by called select "'" || code || "'" into ....

Chris J · Accepted Answer · 2013-08-21 12:36:07Z

2

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.

answered Aug 21, 2013 at 12:36

Chris J

7,7942 gold badges27 silver badges26 bronze badges

Comments

Allan Bowe · Accepted Answer · 2016-05-25 14:02:05Z

1

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

answered May 25, 2016 at 14:02

Allan Bowe

12.7k20 gold badges83 silver badges126 bronze badges

Comments

Tom · Accepted Answer · 2016-05-25 14:27:15Z

0

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

answered May 25, 2016 at 14:27

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Collectives™ on Stack Overflow

single quoting values using sql to create macro variable

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related