execute sql-query within a macro

[dstuder]

/* 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;

Hello everybody,

I have problems with the above code. In table queries there are two variables: a sql-query (`query`) plus an `id`. Now, I'd like to save the results of these queries as well as the corresponding `id` to another table `want`. What do I have to change?

Thanks!

[yabwon · Accepted solution]

Hi,

Try this:

/* The Macro */
%macro run_query(q,id);
  %global _&id.; /* <- change of scope */
  proc sql noprint;
      select count into: _&id. /* <- identifier */
      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 _null_;
set queries;
call execute('%nrstr(%run_query(('||strip(query)||'),'||id||'))'); /* <- "(" and ")" added over querry text */
run;

DATA want2;
  SET queries;
  count = input(symget(cats("_",id)), ?? best32. ); /* collect data */
RUN;

All the best

Bart

[PaigeMiller]

So Bart ( @yabwon ) has clearly given the correct answer, but to @dstuder I point out that before you write macros, you first need to write code that works and does what you want without macros and without macro variables, for one or two cases. That is an essential first step in getting macros to work — if you can't get the code working and doing what you want without macro variables and without macros, then it will never work once you try to write a macro version of the code. The code in your macro %run_query doesn't do what you want, and so wrapping it in a macro will not remedy this.

[yabwon]

Paige ( @PaigeMiller ) I couldn't agree more with your advice to @dstuder

First working "simple" code and only(!) then "macro wrapping".

All the best

Bart

[AhmedAl_Attar]

Here is a slightly modified queries, with a in-data step Proc SQL execution

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

data want(drop=query);
set queries;
rc= dosubl('proc sql noprint;'||query||'; quit;');
count = symget('count');
put count=;
run;

Hope this helps,

Ahmed

[Quentin]

I like the DOSUBL, @AhmedAl_Attar !

The only bit that bothers me is the creation of a global macro variable, which I try to avoid.

One alternative is to wrap the DOSUBL bit inside a function-style macro, and then to pass it data, you need to call it via RESOLVE, e.g.

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


%macro runquery(query) ;
  %local rc count ;
  %let rc=%sysfunc(dosubl(%nrstr(
    proc sql noprint;
      &query; 
    quit;
    )));
  &count
%mend ;

data want ;
  set queries ;
  count=resolve(cats('%runquery(',query,')')) ;

  put (id query count)(=) ;
run ;

[yabwon]

Just for fun, clear datastep no macros, one single proc sql:

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;

DATA _null_;
  call execute('proc sql noprint;');
  do _N_ = 1 by 1 until(EOF);
    SET queries end= EOF;
    call execute(
    'create table work._' || put(_N_,z30.) || /* to keep observations order */
    ' as select a.count
      from (' || strip(query) || ') a;'
    );
  end;
  call execute('quit;');
  stop;
RUN;

data counts;
  set work._0: OPEN=DEFER;
run;

data want;
 merge queries counts;
run;

All the best

Bart

[Quentin]

Nice @yabwon. If we're just having, fun, how about one just one data step to generate one SQL step?

Not sure if below actually works, and I wouldn't want to maintain it.  : )

DATA _null_;
  call execute(
    'proc sql noprint;'
    || 'create table work.want as '
    || ' select a.id, a.query, b.count from ' 
    || '  (select id, query from queries) as a'
    || ','
    || '('
  );
  
  do _N_ = 1 by 1 until(EOF);
    SET queries end= EOF;
    call execute(
      '(SELECT "'||strip(id)||'" as id, '||transtrn(strip(query),'SELECT','')||')'
    );
    if NOT EOF then do ;
      call execute('union corr all') ;
    end ;
  end;
  call execute(
    ') as b ' 
   ||'where a.id=b.id'
   ||';quit ;'
  );
  stop;
RUN;

[yabwon]

But it will handle only up to 255 subqueries.

B-)