DATA Step, Macro, Functions and more

summing by group using SQL

Reply
Occasional Contributor
Posts: 18

summing by group using SQL

If I have a dataset like this

 

MetricProdR_1R_2R_3R_4
NA1245
RA23345
NB21333
RB1234

 

I want to group by Metric and Prod.

Proc sql;
create table want as
select metric, prod, sum(r_1) as r_1, sum(r_2) as r_2, sum(r_3) as r_3, sum(r_4) as r_4
from have 
group by metric, prod;
quit;

 

Here since it has only 4 variables to sum so I can write from R_1 to R_4. What if I had 100 variables to sum? What is the efficient way of doing this?

 

Thanks

Chandan Mishra 

Super User
Posts: 9,932

Re: summing by group using SQL

Posted in reply to chandan_mishra

You can use a wildcard variable name in proc summary:

proc summary data=have sum;
by metric prod;
var r_:;
run;

If you absolutely want to do it in SQL, you can use a macro loop to generate the summations.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Esteemed Advisor
Posts: 5,487

Re: summing by group using SQL

Posted in reply to chandan_mishra

SAS/SQL doesn't support variable lists the way the data step and SAS procs do. One alternative is to do the SQL summations on a transposed version of your dataset.

PG
PROC Star
Posts: 254

Re: summing by group using SQL

Posted in reply to chandan_mishra

The pure SQL way is to use dictionary.tables:

proc sql noprint; /* noprint because we do not want to see the SELECT INTO results as a table */
select catx(' ','sum(',name,') as', name) into :sums separated by ','
  from dictionary columns
  where libname='WORK' 
    and memname='HAVE'
    and upcase(name) like 'R%'
   ;
create table want as select
  metric,
  prod,
  &sums
from have
group by metric,prod
;
quit;
Ask a Question
Discussion stats
  • 3 replies
  • 192 views
  • 4 likes
  • 4 in conversation