SAS proc sql inside %macro

Question

Firstly I have the following table:

data dataset;
    input id $ value;
    datalines;
    A 1
    A 2
    A 3
    A 4
    B 2
    B 3
    B 4
    B 5
    C 2
    C 4
    C 6
    C 8
    ;
run;

I would like to write a macro so that the user can subset the data by giving the id value. I do proc sql inside the macro as follows:

%macro sqlgrp(id=,);

proc sql;
        create table output_&id. as
        select *
        from dataset
        where id = '&id.'
        ;
    quit;
%mend;

%sqlgrp(id=A); /*select id=A only*/

I am able to generate the output_A table in the WORK library, however it has zero (0) observations.

Why is this not working?

mjsqu mjsqu · Accepted Answer · 2014-12-04T07:59:54

You need to use double quotes when referring to macro variables.

Current Code

%macro sqlgrp(id=,);

proc sql;
        create table output_&id. as
        select *
        from dataset
        where id = '&id.'
        ;
    quit;
%mend;

%sqlgrp(id=A); /*select id=A only*/

Looks for values of id that are literally '&id.'. You can test this by creating this dataset:

data dataset;
    input id $ value;
    datalines;
&id. 2
A    2
    ;
run;

Now, use %let to set the value of the macro variable id:

%let id=A;

Run a quick test of the functionality difference between single and double quotes. Notice the titles also contain single and double quotes, so we can see exactly what has happened in the output:

proc sql;
  title 'Single Quotes - where id=&id.';
  select *
  from dataset
  where id='&id.';

  title "Double Quotes - where id=&id.";
  select *
  from dataset
  where id="&id.";
  title;
quit;

Correct Code

%macro sqlgrp(id=,);

proc sql;
        create table output_&id. as
        select *
        from dataset
        where id = "&id."
        ;
    quit;
%mend;

%sqlgrp(id=A); /*select id=A only*/

The double quotes allow the macro variable &id to resolve to 'A', which will return results based on your input.

SAS proc sql inside %macro

2 Answers