CODEWITHSUNDEEP
sqlsas
Oliver
Oliver

Reputation: 194

SAS: Summing to different levels

I have a data set with several heirachical variables: Region State County City District

There is a series of variables which will be counted, summed, etc. for each combination of the above variables. This is simple enough with a basic proc sql, EXCEPT that the output file needs to include a row for the totals at each level. So if there are 4 Districts for a particular City, there would be 5 rows, for example.

One way of generating the fifth row would be something like this:

proc sql;
create table district_sum as
select Region, State, County, City, 'All Districts' as District, bla, bla, bla...

This would give me totals at the City level, and then I could repeat this process for each level. But I am thinking there must be a better way to do this than with a series of similar sql steps.

Upvotes: 0

Views: 178

Answers (3)

toby16custom
toby16custom

Reputation: 1

To build on Dmitry Shopin's answer, If you are using SAS EG you can utilize the summary tables wizard. Easily play and see what the various levels are.

You can then source code from that as well to derive Proc Tabulate.

Upvotes: 0

Dmitry Shopin
Dmitry Shopin

Reputation: 1763

You can use PROC TABULATE to generate easily reports with various nested variables and subtotals:

PROC TABULATE data=yourdata;
   CLASS Region State County City District;
   VAR Sales;
   TABLE Region*(State*(County*(City*(District ALL) ALL) ALL) ALL) ALL,
        Sales*sum;
RUN;

Upvotes: 0

DomPazz
DomPazz

Reputation: 12465

Try PROC SUMMARY.

proc summary data=foo;
class region state county city;
var bar;
output out=outData sum=sum;
run;

Upvotes: 1

Related Questions