topic Run SQL statements stored in a variable in SAS Programming

Run SQL statements stored in a variable

Mishka1 — Thu, 27 Oct 2011 15:02:56 GMT

Hello,

I have fully formed SQL Select statements stored in a variable. I want to run them and append the results of each to a common data set.

Each SQL statement has the same fields it returns so they will be able to be appended.

I can copy the SQL statement right out of the table and paste it into a proc sql statement and run it, but I can't get a do loop to run through each SQL statement and I have no idea how to get them to append to just one table without overwriting it each time.

Though now I'm thinking that I do have the ability to change the Select statement to an append...if that helps.

Below is the SQLtable that I have and the ResultsTable I need.

SQLtable:

QueryName | SQLStatement

Qry1 | Select Field1, Field2 From cwork.trans Where Field2= blue

Qry2 | Select Field1, Field2 From cwork.trans Where Field2= red

ResultsTable:

QueryName | Field1 | Field2

Thanks for any help.

Run SQL statements stored in a variable

Peter_C — Thu, 27 Oct 2011 15:46:23 GMT

call execute() looping over these "variables" might achieve what you need

something like

data _null_;

if _n_ 1 then

call execute( '

proc sql noprint ;

' );

else call execute( '

outer union

' );

if end_of_data then

do;

call execute( '

; quit ;

' );

stop;

end;

set SQLtable end=end_of_data;

call execute(

SQLstatement

);

run;

Re: Run SQL statements stored in a variable

art297 — Thu, 27 Oct 2011 15:47:12 GMT

I think you can do what you want with something like:

*create some test data;

data trans;

set sashelp.class (rename=(

height=Field1));

length Field2 $4;

Field2="red";

output;

Field2="blue";

output;

run;

data SQLtable;

informat QueryName $5.;

informat SQLStatement $80.;

input QueryName SQLStatement &;

cards;

Qry1 Select Field1, Field2 From work.trans Where Field2= 'blue'

Qry2 Select Field1, Field2 From work.trans Where Field2= 'red'

;

proc sql noprint;

select substr(SQLStatement,1,7)||'"'||QueryName||

'" as QueryName,'||substr(SQLStatement,7)

into :queries

separated by " outer union corresponding "

from SQLtable

;

quit;

proc sql;

create table want as

&queries.

;

quit;

If you want an explanation of why that would work, search the following document for the word "append":

http://www2.sas.com/proceedings/sugi29/269-29.pdf

Re: Run SQL statements stored in a variable

Tom — Thu, 27 Oct 2011 17:42:46 GMT

You will need to do code generation.

You can use a data step to write code to file and then INCLUDE it.

Or use CALL EXECUTE to stuff the code onto the stack.

Or write a macro and generate macro calls.

My question is what query do you want to generate to get your result.

I would assume you want something like.

create table want as

select * from (select "qry1" as QueryName),(... select statement...)

union

select * from (select "qry2" as QueryName),(... select statement...)

;

Watch out that PROC SQL has a limit on the number of tables that can be referenced in one statement.

Re: Run SQL statements stored in a variable

Howles — Thu, 27 Oct 2011 22:16:19 GMT

This could be simpler if the canned queries incorporated the IDs, as in

cards;

Select "Qry1" as QueryName, Field1, Field2 From work.trans Where Field2= 'blue'

Select "Qry2" as QueryName, Field1, Field2 From work.trans Where Field2= 'red'

;

Also, why not use RESET to manage the state of the PRINT/NOPRINT option so that only one SQL step is needed.

Run SQL statements stored in a variable

Mishka1 — Fri, 28 Oct 2011 12:40:03 GMT

I incorporated the QueryName into the select like you suggested. Thanks.

Run SQL statements stored in a variable

Mishka1 — Fri, 28 Oct 2011 12:41:37 GMT

I am hitting that limit now. Thanks for the heads up.
"A maximum of 256 tables can be processed in a single Proc SQL statement"

I'll be posting another question on a work-around if I can't figure it out in the next few hours.

Run SQL statements stored in a variable

Mishka1 — Fri, 28 Oct 2011 12:49:19 GMT

Thanks for the paper. I'm native to SQL so using it backwards, I learned more about SQL to SAS equivalents!

Run SQL statements stored in a variable

Tom — Fri, 28 Oct 2011 13:45:47 GMT

You could use the INSERT statement in SQL.

create table want as &query1;

insert into want &query2;

insert into want &query3;

....

You could instead create views in SQL and then use a DATA step to combine them. DATA steps do not have the same limits.

proc sql;

create view v001 as &query1;

create view v002 as &query2;

....

quit;

data want ;

set v001 v002 .... ;

run;

Run SQL statements stored in a variable

Mishka1 — Fri, 28 Oct 2011 18:32:56 GMT

Hi Tom, Thanks for the Insert suggestion. That seemed to be the work-around I needed.

I'm trying to append all the views now but am getting an error.

I have about 300 views so I've tried to create an array to call in the Set statement so I don't have to list them out but I get an error. Hopefully I'm making an easy mistake. Thanks.

data _null_;

set LogicTable; /* table that has the viewnames*/

CALL SYMPUT('ViewNames', ViewName);

stop;

run;

data Queued;

set &ViewNames;

run;

Error is with the semicolon after &ViewNames;

It says,

Line generated by the macro variable "VIEWNAMES".

1 .

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, END,

KEY, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

Run SQL statements stored in a variable

Tom — Fri, 28 Oct 2011 18:56:45 GMT

I see two problems with that.

1) From the error message it looks like the variable ViewName in the table LogicTable is numeric and missing so CALL SYMPUT is setting macro variabel ViewNames to a dot to represent a missing value. To see what the macro variable ViewNames received throw in a %PUT "&viewnames"; statement.

2) Unless the table LogicTable has only one observation you cannot use CALL SYMPUT to generate a list.

To generate a list into a macro variable it is easiest to use PROC SQL.

proc sql noprint ;

select viewname into :viewnames separated by ' ' from logictable;

quit;

Note that the separated by clause is critical or else PROC SQL will just give you the value from the first observation if selects.

Run SQL statements stored in a variable

Mishka1 — Fri, 28 Oct 2011 20:33:53 GMT

YAY. That worked perfectly. Now when I merge them, I'm getting an error because some of the views are empty. I can post this as a separate question if you think it should be. I was thinking I could make each view into a data set but that seems like a lot of space for something that probably has a more elegant answer.