pro sql number;
create table daily_total as
select distinct trans_dt,
sum((select (quantity * unit_price)
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';
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;