BookmarkSubscribeRSS Feed
niemannk
Calcite | Level 5
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.
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Appears the SAS PROC SQL syntax doesn't support such constructs, to dynamically resolve the FROM.

Scott Barry
SBBWorks, Inc.
Patrick
Opal | Level 21
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
Calcite | Level 5
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
Calcite | Level 5
would have been nice though...

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1250 views
  • 0 likes
  • 3 in conversation