topic Re: Create new table via Proc SQL with Multiple Select Statement Results in SAS Programming

Create new table via Proc SQL with Multiple Select Statement Results

ijm_wf — Thu, 19 Sep 2019 20:25:38 GMT

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;

Re: Create new table via Proc SQL with Multiple Select Statement Results

ballardw — Thu, 19 Sep 2019 20:54:33 GMT

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

Re: Create new table via Proc SQL with Multiple Select Statement Results

PGStats — Thu, 19 Sep 2019 21:48:34 GMT

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;

Re: Create new table via Proc SQL with Multiple Select Statement Results

Ksharp — Fri, 20 Sep 2019 12:27:58 GMT

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;