topic Re: SQL problem in SAS Procedures

SQL problem

niemannk — Thu, 05 Aug 2010 16:30:38 GMT

Hi,
the following sql statement returns the string: mylib.tbl20

proc sql;
select trim(libname)||'.'||trim(memname)
from (select * from gmo.all_tables where date_=(select max(date_) from gmo.all_tables))
;
quit;
In gmo.all_tables I simply have a dataset (actually it is a view) on some tables that get created on daily basis.

Now I want to select something from the table mylib.tbl20 (preferably as a view).

This doesn't work
proc sql;
select * from (
select trim(libname)||'.'||trim(memname)
from (select * from gmo.all_tables where date_=(select max(date_) from gmo.all_tables))
)
;
quit;

Anyway I can let SAS know the result of the embedded query is a table it should select from?? Thanks.

Re: SQL problem

sbb — Thu, 05 Aug 2010 16:34:15 GMT

Create a macro variable in a preceding query and use that string-combination . in your subsequent query. Have a look at using ":INTO" with your SELECT in PROC SQL, to generate a macro variable.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search, this topic / post:

proc sql into macro variable site:sas.com

Re: SQL problem

niemannk — Thu, 05 Aug 2010 16:42:01 GMT

yes this a solution. I however dreamed about a view that simply could be used from anywhere without having an other sql statement run prior to that.

Re: SQL problem

sbb — Thu, 05 Aug 2010 17:37:11 GMT

Appears the SAS PROC SQL syntax doesn't support such constructs, to dynamically resolve the FROM.

Scott Barry
SBBWorks, Inc.

Re: SQL problem

Patrick — Thu, 05 Aug 2010 21:18:46 GMT

Your construct as such seems o.k. and it works for me (SAS 9.2) for sashelp.class:

proc sql;
create view Vclass as
select *
from sashelp.class
;

select * from (
select cats(sex,'.',age)
from (select * from Vclass where age=(select max(age) from Vclass))
)
;
quit;

What error do you get (post the log)?

Re: SQL problem

niemannk — Fri, 06 Aug 2010 06:38:20 GMT

thx Patrick, no error, but your inner query returns a table that the exterior query uses. I guess ssb is right. Whatever you create with a SELECT FROM is considered as a data table.

Re: SQL problem

niemannk — Fri, 06 Aug 2010 06:38:59 GMT

would have been nice though...