topic proc sql sum several variables and group by in SAS Programming

proc sql sum several variables and group by

Sir_Highbury — Wed, 06 Jul 2016 14:41:22 GMT

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

Re: proc sql sum several variables and group by

jklaverstijn — Wed, 06 Jul 2016 14:59:41 GMT

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.

Re: proc sql sum several variables and group by

ballardw — Wed, 06 Jul 2016 15:03:53 GMT

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;

Re: proc sql sum several variables and group by

Astounding — Wed, 06 Jul 2016 15:14:52 GMT

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.

Re: proc sql sum several variables and group by

sh0e — Wed, 06 Jul 2016 18:09:37 GMT

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