SAS: Run SQL-query using a macro

0
votes

Using an answer from this thread, I was trying to get to work the following code. I have a list of sql-queries in a table plus an id for each query. Now I'd like to have the results of these queries plus the id as another table.

/* The Macro */
%macro run_query(q,id);
  proc sql noprint;
      select count into: count
      from (&q.) a;
  quit;
%mend;

/* Some fake-data */
DATA queries;
INPUT id :$12. query :$3000.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
01,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Married=1
0101,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Boy=1
0102,SELECT COUNT(*) AS count FROM sashelp.bweight WHERE Black=1
;
RUN;

/* Make a copy of the dataset */
DATA want;
SET queries;
RUN;

/* Insert the results */
data want;
set queries;
call execute(%nrstr(%run_query('||query||','||id||')));
run;

Can anyone see, what the problem is? The error report looks like this:

enter image description here

2
sassas-macroproc-sql
Show the text of the lines from the actual log (not the summary) so you can see what code is being flagged. Make sure to turn MPRINT option on so you can see in the log the code the macro generated. – Tom
Why does the macro have the second input? It is not used. I do not see where you have defined the target macro variable COUNT in advance. If it does not exist it will be local to the macro and disappear. But you seem to be over writing the value with the next call anyway. – Tom
execute() takes string argument. You've provided open code. Could you try actually quoting it with single quotes rather than using %nrstr. – samkart
Also, it's helpful if you provide the full log that SAS generated – samkart

2 Answers

1
votes

In part /* Insert the results */ you're basically sending all your values/results into dev null with the data step:

data _null_;

Instead try:

data want;
0
votes

you can try this for the second part

use proc sql in macro to extract the count, and build dataset using variable count1,count2,count3

       %macro a;
    proc sql;
    select count(*) into :count1 FROM sashelp.bweight WHERE Married=1;
    SELECT COUNT(*) into :count2 FROM sashelp.bweight WHERE Boy=1;
    SELECT COUNT(*) into :count3 FROM sashelp.bweight WHERE Black=1;
    quit;

    DATA queries;
    length id $12 query $3000;
    format id $12. query $3000.;
    infile datalines delimiter=','; 
    input id $ query $;
    datalines;
    01,&count1
    0101,&count2
    0102,&count3
    ;
    run;
  %mend a;

/*Call above maco*/
    %a;