SQL problem

[niemannk]

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.

[sbb]

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

[niemannk]

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.

[sbb]

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

Scott Barry
SBBWorks, Inc.

[Patrick]

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)?

[niemannk]

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.

[niemannk]

would have been nice though...