Create new table via Proc SQL with Multiple Select Statement Results

[ijm_wf]

I'm trying to create a new table called table_info using proc sql. The table will consist of three variables: var1, var2, var3 with each of the variables being the result of a select statement. How can I modify the following code? Please respond with short code examples.  Thanks.

 

proc sql;
create table table_info as
select count(*) as var1 from dbschema.tab1;
select count (distinct id) as var2 from dbschema.tab2;
select max (run_dt) as var3 from dbschema.tab3;
quit;

[ballardw]

For your exact example this should work

proc sql;
create table table_info as
  select a.var1, b.var2, c.var3
  from 
      (select count(*) as var1 from dbschema.tab1) as a,
      (select count (distinct id) as var2 from dbschema.tab2) as b,
      (select max (run_dt) as var3 from dbschema.tab3) as c
  ;
quit;

You will likely get a note about a cartesian product in the log.

If you have something in the individual tables to group the data then likely you will want some form of Join on the group by variables

[PGStats]

If you want to avoid the note about cartesian join, you can do:

proc sql;
create table myTable (var1 num, var2 num, var3 num);
insert into myTable
set 
    var1 = (select max(weight) from sashelp.class),
    var2 = (select max(weight) from sashelp.cars),
    var3 = (select max(weight) from sashelp.heart);
quit;

[Ksharp]

proc sql;
create table table_info as
select 
(select count(*) as var1 from dbschema.tab1 ) as var1,
(select count (distinct id) as var2 from dbschema.tab2 ) as var2,
(select max (run_dt) as var3 from dbschema.tab3 ) as var3

from dbschema.tab1(obs=1) ;
quit;