turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- summing by group using SQL

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-23-2017 04:12 PM

If I have a dataset like this

Metric | Prod | R_1 | R_2 | R_3 | R_4 |

N | A | 1 | 2 | 4 | 5 |

R | A | 2 | 33 | 4 | 5 |

N | B | 21 | 3 | 3 | 3 |

R | B | 1 | 2 | 3 | 4 |

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chandan_mishra

09-23-2017 04:16 PM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chandan_mishra

09-23-2017 06:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chandan_mishra

09-28-2017 08:19 AM

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;