PROC SQL subquery/correlated query

Question

pro sql number;
   create table daily_total as
   select distinct trans_dt,
      sum((select (quantity * unit_price) 
           from transaction_detail 
           where subcat_num = 1111 and trans_dt = a.trans_dt))
   from transaction_detail as a
   where trans_dt between '2015-01-01' and '2015-01-02';
quit;

I understand I can achieve the samething with group by but I want to do this by subquery for the learning experierence.

I essentially want to select the two distinct dates and return the sum of each individual transaction for the subcategory on that particular day.

Thank you.

You still have to use the group by. Also, you need to give computed columns an alias for the "Create Table". Preferable with typcast to explicitly set the datatypes. — Amir Pelled, Aug 30 '15 at 10:33

score 1 · Accepted Answer · answered Aug 30 '15 at 17:05

In SQL, aggregate functions like SUM, AVG, MAX, MIN (depending on the SQL engine) do not run on subqueries themselves.

Consider the following adjustment where SUM is used inside the subquery. Also, I assume you want the subquery's date range to correspond to the outer query's date and one day in future. Hence, I use SAS's INTNX() function.

pro sql;
  create table daily_total as

  select distinct a.trans_dt,
    (select sum(b.quantity * b.unit_price)
     from transaction_detail As b
     where b.subcat_num = 1111  
     and (b.trans_dt between a.trans_dt 
          and intnx('day', a.trans_dt, 1)) As transaction_sum
   from transaction_detail a;

quit;

Haha...Indeed `group by` reads better. This could be a simple aggregate query grouped by trans_dt without the `distinct` clause and subquery. — Parfait, Aug 31 '15 at 19:16

PROC SQL subquery/correlated query

1 Answers1