Help using Base SAS procedures

SQL problem

Reply
Contributor
Posts: 29

SQL problem

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: SQL problem

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
Contributor
Posts: 29

Re: SQL problem

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: SQL problem

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

Scott Barry
SBBWorks, Inc.
Respected Advisor
Posts: 3,887

Re: SQL problem

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)?
Contributor
Posts: 29

Re: SQL problem

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.
Contributor
Posts: 29

Re: SQL problem

would have been nice though...
Ask a Question
Discussion stats
  • 6 replies
  • 196 views
  • 0 likes
  • 3 in conversation