topic Error: Summary functions nested in this way are not supported in SAS Programming

Error: Summary functions nested in this way are not supported

PegaZeus — Thu, 23 Apr 2020 18:37:20 GMT

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.

Re: Error: Summary functions nested in this way are not supported

Tom — Thu, 23 Apr 2020 18:46:15 GMT

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.

Re: Error: Summary functions nested in this way are not supported

PegaZeus — Thu, 23 Apr 2020 19:27:05 GMT

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.

Re: Error: Summary functions nested in this way are not supported

PGStats — Thu, 23 Apr 2020 21:01:56 GMT

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.