Sas Proc Sql Inside %macro

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

Solution 1:

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;
    inputid $ value;
    datalines;
&id. 2
A    2
    ;
run;

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

%letid=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.

Solution 2:

Just a simple rewrite of the previous answer which passes 'in' and 'out' through a signature of the macros

%macro sqlgrp(in=, id=, out=);
   proc sql noprint;
      create table &out. asselect * from &in. where id = "&id.";
   quit;
%mend sqlgrp;