proc sql sum several variables and group by

[Sir_Highbury]

Dear experts,

let's assume the following variables: shop_ID, bolean_variable, revenue201601, revenue201602, revenue201603, revenue201603

for a total of 10 observations.

how can set a proc sql as it follows: proc sql; create table abc as select   shop_ID, bolean_variable, sum (revenue:) from xyz group by  shop_ID, bolean_variable ;quit;

How can I specify to do it for some variables without listing them one by one?

Thanki in advance and BRs, SH

[jklaverstijn]

You cannot specify variable ranges or wildcards in the SELECT clause as you would have in a datastep. But you can use KEEP on the table:

proc sql;
   select *
   from have(keep=shop_id revenue2016:);
quit;

You could also use shortcut notation like VAR1-VAR8, VAR1--VAR8, _NUMERIC_ etc. But always on the KEEP, never on the SELECT.

Regards, Jan.

[ballardw · Accepted solution]

Are you looking to sum within year the 4 revenue variables and then get a single aggregate total or do you want 4 aggregate sums?

I think if your main criteria is the use of variable lists you may want to look to something like Proc summary or means instead of sql.

proc summary data= xyz nway;

   class shop_id bolean_variable;

   var revenue: ;

   output out=abc (drop= _:) sum=;

run;

[sh0e]

This is DEFINITELY a task for PROC SUMMARY as @ballardw notes.

[Astounding]

As was noted, there are simpler solutions than using SQL.  But if you are determined to use a SQL solution, macro language can help.  Your intention makes a difference.  Which of these would be correct within a SELECT statement?

sum(revenue1) as revenue1, sum(revenue2) as revenue2, sum(revenue3) as revenue3

vs.

sum(revenue1, revenue2, revenue3) as tot_revenue

If PROC SUMMARY works for what you need, that would be simpler.  No need to mess with macro language unless necessary.