DATA Step, Macro, Functions and more

proc sql sum several variables and group by

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

proc sql sum several variables and group by

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

 


Accepted Solutions
Solution
‎07-07-2016 09:12 AM
Super User
Posts: 10,488

Re: proc sql sum several variables and group by

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= _Smiley Happy sum=;

run;

View solution in original post


All Replies
Super Contributor
Posts: 408

Re: proc sql sum several variables and group by

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.

Solution
‎07-07-2016 09:12 AM
Super User
Posts: 10,488

Re: proc sql sum several variables and group by

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= _Smiley Happy sum=;

run;

Contributor
Posts: 25

Re: proc sql sum several variables and group by

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

Super User
Posts: 5,081

Re: proc sql sum several variables and group by

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 596 views
  • 1 like
  • 5 in conversation