SAS proc sql – is this the correct usage?

This works but wanted a health check as to if i have done this correct with proc sql connect to i.e do i need to have separate “select * from connection to’s”?

proc sql;
connect to database (database=test user="&srvuser" password="&srvpass") ;
create table bob as 
select t1._ as bob1 
      ,t2._ as bob2 
      ,t3._ as bob3
      ,t1._-t2._ as bob4
from (select * from connection to database
(Select count(*)
        from DAVE.IMAGE_MSG_LOG 
        where RR_MSG_CODE  = 'D' and RR_MSG_SEQ_NO = 1)) t1
,(select * from connection to database
(Select count(*)
        from DAVE.IMAGE_MSG_LOG 
        where RR_MSG_CODE  = 'R')) t2
,(select * from connection to database
(Select count(*)
        from DAVE.IMAGE_MSG_LOG 
        where RR_MSG_CODE  = 'R' and RR_MSG_TIME_STAMP IS NULL)) t3;
disconnect from database;
quit;
run;

Thanks

Answer

Since you are pulling from the same database just push the join into the database. While you are at it assign names to the variables when you create them.

create table bob as 
select bob1 
     , bob2 
     , bob3
     , bob1-bob2 as bob4
from connection to database 
(select t1.bob1
      , t2.bob2
      , t3.bob3 
 from
  (select count(*) as bob1
   from DAVE.IMAGE_MSG_LOG 
   where RR_MSG_CODE  = 'D' and RR_MSG_SEQ_NO = 1
  ) t1
, (select count(*) as bob2
   from DAVE.IMAGE_MSG_LOG 
   where RR_MSG_CODE  = 'R'
   ) t2
, (select count(*) as bob3
   from DAVE.IMAGE_MSG_LOG 
   where RR_MSG_CODE  = 'R' and RR_MSG_TIME_STAMP IS NULL
  ) t3
);

Since you are generating those counts from querying the same table (dataset) you can to it with a single query by using CASE instead of WHERE.

(select sum(case when (RR_MSG_CODE  = 'D' and RR_MSG_SEQ_NO = 1) then 1 
            else 0 end) as bob1
      , sum(case when (RR_MSG_CODE  = 'R') then 1 
            else 0 end) as bob2
      , sum(case when (RR_MSG_CODE  = 'R' and RR_MSG_TIME_STAMP IS NULL) then 1 
            else 0 end) as bob3
 from DAVE.IMAGE_MSG_LOG 
)

Although you might want to add the common conditions to the where clause to have it process fewer records.

where RR_MSG_CODE in ('R','D')