Linus sorry to jump into this thread but I am also curious to your and PGstats responses. I do know that sql can have different select clauses over group by clauses, but since Anna_nag has to do it twice how would this work in one sql statement? Or are you saying to simply do it in two sql statements and then merge them (which becomes your big table) and then live with the performance issues? Ex: I know you can do this, and this is a "solution" to the original posters question. proc sql; create table avgtwo as select a1, a2,a3, a4 , avg(revenue) as avg2 from big group by a1,a2,a4; quit; Which would calculate the average over a1,a2, and a4 across the assocaited rows (including A3). however how would you then calculate the average of a1,a2,a3 without doing another create table and merge? I realize the merge is more efficient since the tables would share all variables and it would only be one merge. I'm just curious if this is what you were implying. So the final code would look like proc sql; create table avgone as select a1, a2,a3, a4 , avg(revenue) as avg1 from big group by a1,a2,a3; quit; proc sql; create table avgtwo as select a1, a2,a3, a4 , avg(revenue) as avg2 from big group by a1,a2,a4; quit; proc sort data=avgone; by a1 a2 a3 a4; run; proc sort data=avgtwo; by a1 a2 a3 a4; run; data answer; merge avgone avgtwo; by a1 a2 a3 a4; run;
... View more
