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

- Subscribe to 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
- Subscribe to 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
- Subscribe to 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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to 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
- Subscribe to 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;