CODEWITHSUNDEEP
sas
D. Studer
D. Studer

Reputation: 1875

SAS: execute sql query strings out of a table?

If I have a table containing sql query strings like this (column query)

id  query                                 n
——————————————————————————————-—————————————
1   select count(*) from tab where x=...
2   select count(*) from tab where x=...
...

In SAS is there a way to save the results of these queries in another column n?

Upvotes: 0

Views: 679

Answers (3)

user667489
user667489

Reputation: 9569

Just for fun - an approach using only proc sql:

data queries;
input id :8. query :$100.;
infile datalines dsd;
datalines;
1,select count(*) as count from sashelp.class where sex = 'F'
2,select count(*) as count from sashelp.class where sex = 'M'
;
run;

proc sql noprint;
  select catx(' ','select ', id, ' as id,', substr(query, 7)) into :code
    separated by ' union all ' 
    from queries;
  create table want as
    &code;
quit;

This will only work as long as the total combined length of all your queries fits within the maximum allowed length of a single macro variable. If in doubt, check the value of the MVARSIZE system option.

Upvotes: 1

Richard
Richard

Reputation: 27498

If all the queries have a result set of one row & one column, you are dealing with a scalar result which can be placed in a data set variable.

Interaction features to use:

  • dosubl, execute a separate SAS code block while a STEP is running. A Proc SQL query in this case.

    • The DOSUBL function enables the immediate execution of SAS code...
      DOSUBL executes code in a different SAS executive (known as a side session)

  • into :macro-variable, store result in a macro variable
  • symget, retrieve result from macro environment

If you are querying the same native SAS table over and over again, you might consider including:

  • sasfile, opening the data set into memory so disk i/o occurs only once when querying the table repeatedly

Example

The queries, as stated in the question, do NOT include an INTO clause. On the presumption that each query does not have INTO AND has a single scalar result, the INTO clause can be introduced into the query string via TRANWRD.

data have;
  input;
  query = _infile_;
datalines;
select count(*) from sashelp.class where name between 'A' and 'F'
select count(*) from sashelp.class where name between 'F' and 'K'
select count(*) from sashelp.class where name between 'K' and 'O'
select count(*) from sashelp.class where name between 'O' and 'S'
select count(*) from sashelp.class where name between 'S' and 'ZZ'
select count(*) from sashelp.class where name between 'A' and 'K'
select count(*) from sashelp.class where name between 'A' and 'O'
select count(*) from sashelp.class where name between 'A' and 'S'
select count(*) from sashelp.class where name between 'A' and 'ZZ'
;

sasfile sashelp.class open;

data want;
  set have;

  * tweak 'presumed to be scalar' query;

  length into_query $1000; drop into_query;
  into_query = tranwrd (query, 'from', 'into :scalar_result from');

  rc = dosubl('proc sql noprint; ' || into_query);

  query_result = symget('scalar_result');
run;

sasfile sashelp.class close;

ods listing; proc print; run;

have proc print

                                                                                   query_
Obs                                  query                                   rc    result

 1     select count(*) from sashelp.class where name between 'A' and 'F'      0       4
 2     select count(*) from sashelp.class where name between 'F' and 'K'      0       8
 3     select count(*) from sashelp.class where name between 'K' and 'O'      0       2
 4     select count(*) from sashelp.class where name between 'O' and 'S'      0       3
 5     select count(*) from sashelp.class where name between 'S' and 'ZZ'     0       2
 6     select count(*) from sashelp.class where name between 'A' and 'K'      0      12
 7     select count(*) from sashelp.class where name between 'A' and 'O'      0      14
 8     select count(*) from sashelp.class where name between 'A' and 'S'      0      17
 9     select count(*) from sashelp.class where name between 'A' and 'ZZ'     0      19

Upvotes: 1

J_Lard
J_Lard

Reputation: 1103

You should be able to do this by calling a macro within a data step, but depending on how the query is written, you'll have to call it in a sub-query in proc sql.

%macro run_query(q,id);

/* This section expects that your original query assigns the alias 'count' to the result value */
proc sql noprint;
select count into: count * This assigns the result count into a macro variable count;
from (
&q.) a;
quit;

data want;
set want;
if id=&id. then result = &count.;
run;

%mend;

At this point we have the macro run_query which takes the query, and id as inputs. Next, we will copy the dataset and then iterate through the original dataset updating each result based on the id.

data want;
set have;
run;

data _null_;
set have;
call execute(%nrstr(%run_query('||query||','||id||'))');
run;

Upvotes: 1

Related Questions