Error: Summary functions nested in this way are not supported

[PegaZeus]

I have a proc sql where I'm trying to sum off of a calculated field like this:

proc sql;
  create table x as select
  sum(case  
        when t1.admin_dt >=  t2.fromdate1 then t2.unitcost1 
        when t1.admin_dt >=  t2.fromdate2 then t2.unitcost2 
        else t2.unitcost3 
      end) as price_unit_cost,
  sum(case 
        when calculated price_unit_cost is not null then calculated price_unit_cost * t1.quantity
        else t2.unitcost1 * t1.quantity
      end) as alt_price_unit_cost
  from my_table t1 left join my_table2 t2 on (t1.code=t2.code);
quit;

However it gives me the error: ERROR: Summary functions nested in this way are not supported.

[Tom]

Hard to tell what you are actually trying to do since you do not have any GROUP BY clause.  Is PRICE_UNIT_COST going to be just one number?  If it is just one number then you need to calculate the price_unit_cost first. either as a separate query.  Or in a sub-query.

( sum(case  
        when t1.admin_dt >=  t2.fromdate1 then t2.unitcost1 
        when t1.admin_dt >=  t2.fromdate2 then t2.unitcost2 
        else t2.unitcost3 
      end) as price_unit_cost
  from my_table t1 left join my_table2 t2 on (t1.code=t2.code)
) summary

And then figure out how to join that with your other query.

Or do you want different numbers for different observations?  In that case just remove the SUM() from around the CASE ... END.

[PegaZeus]

There is actually a group by. I forgot to include. It selects/groups by code_p, patient_id, state. Well the values should be different, summed by each group.

[PGStats · Accepted solution]

Why not :

proc sql;
  create table x as select
  sum(case  
        when t1.admin_dt >=  t2.fromdate1 then t2.unitcost1 
        when t1.admin_dt >=  t2.fromdate2 then t2.unitcost2 
        else t2.unitcost3 
      end) as price_unit_cost,
  sum(case  
        when t1.admin_dt >=  t2.fromdate1 then t2.unitcost1 * t1.quantity
        when t1.admin_dt >=  t2.fromdate2 then t2.unitcost2 * t1.quantity
        else t2.unitcost3 * t1.quantity
      end) as alt_price_unit_cost
  from my_table t1 left join my_table2 t2 on (t1.code=t2.code)
  group by code_p, patient_id, state;
quit;

if price_unit_cost is missing, so will alt_price_unit_cost.