SAS Subquery in Proc Sql

Question

I have a table containing a list of libnames and their tables. I'd like to create a subquery to return the minimum createdon date for each listed table, passing the libname and table as the libname.table used in the subquery.

I've created the following:

proc sql;
create table createdon_min as
select *,
cats("(select min(createdon) from "||strip(libname)||"."||strip(table_name)||")") as CREATEDON_FRM
from all_tables1
where freq="DAILY" AND part_date="CREATEDON";
quit;

No error is generated, but the createdon_frm value doesn't execute, it merely creates a string value like "(select min(createdon) from pvaprov.institutional)".

The libname and table resolved correctly, but how do I get the statement to execute as a subquery?

I am not aware that SAS proc sql support dynamic SQL/prepared statements. — Gordon Linoff
Use the SELECT INTO construct and pass your dynamic statement(s) into macro variables. Then use a macro loop to build a new PROC SQL to execute each statement. — Chris J

Laurent de Walick Laurent de Walick · Accepted Answer · 2014-07-28T08:52:54

You cannot dynamically call sql queries from another sql query. This solutions uses the datastep and call execute to do the proc sql and append the result to a base dataset.

data want;
 length mincreatedon 8.;
 stop;
run;

data _null_;
 set all_tables1;
 where freq="DAILY" AND part_date="CREATEDON";
 call execute("proc sql;create table a as select min(createdon) as mincreatedon from "||strip(libname)||"."||strip(table_name)||";quit;proc append base=want data=a;run;");
run;

SAS Subquery in Proc Sql

1 Answers