topic execute sql-query within a macro in SAS Programming

execute sql-query within a macro

dstuder — Tue, 21 Apr 2020 12:04:16 GMT

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

Re: execute sql-query within a macro

yabwon — Tue, 21 Apr 2020 12:25:21 GMT

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

Re: execute sql-query within a macro

PaigeMiller — Tue, 21 Apr 2020 12:45:50 GMT

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.

Re: execute sql-query within a macro

yabwon — Tue, 21 Apr 2020 12:50:30 GMT

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

Re: execute sql-query within a macro

AhmedAl_Attar — Tue, 21 Apr 2020 12:53:50 GMT

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

Re: execute sql-query within a macro

Quentin — Tue, 21 Apr 2020 17:24:50 GMT

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 ;

Re: execute sql-query within a macro

yabwon — Tue, 21 Apr 2020 18:38:35 GMT

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

Re: execute sql-query within a macro

Quentin — Tue, 21 Apr 2020 19:44:29 GMT

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;

Re: execute sql-query within a macro

yabwon — Tue, 21 Apr 2020 20:03:23 GMT

But it will handle only up to 255 subqueries.

B-)