Aggregate and Calculate by Jurisdiction and Overall

Solved
Regular Contributor
Posts: 206

Aggregate and Calculate by Jurisdiction and Overall

[ Edited ]
```   infile datalines missover;
informat buildingname \$10. buildingtype \$8. responded \$1. jurisdictn \$9. enr best5. ;
input buildingname 1-10  buildingtype  responded  jurisdictn  enr  ;
cards;
happy days private Y nottingha 100
learning   public  N nottingha
buildingbl private Y nottingha 55
montessori private N nottingha
bean stalk private Y brunswick 55
learning   private Y brunswick 22
early lear private Y brunswick 255
childtime  private N brunswick
risingstar public  N brunswick
doodlebugs public  Y brunswick 34
;

run;

proc means data=have mean;
class jurisdictn buildingtype;
var enr;
run;

proc tabulate data=have;
class jurisdictn buildingtype;
var enr;
table  jurisdictn,
buildingtype * enr*mean;
run;

```

Hi, I forgot to ask in a previous post, how to get output overall and by jurisdiction. The code above gives output by jurisdication.  How do I get output with mean for all public buildings listed on the first line,  the individual jurisdictions below it? Same for private

I have the data above, I need to sum enr and divide by number that responded 'Y' get the following output

Calclation:

Jurisdiction  Private                      Public

Overall     (100+55+55+22+255)/5         34/1

nottingha    (100+55)/2
brunswick     (55+22+255)/3            34/1

Results:

Jurisdiction  Private    Public

Overall           97.4        34.0

Jurisdication

Nottingha         77.5
Brunswick      110.7      34.0

Thanks!

Accepted Solutions
Solution
‎05-01-2017 08:20 PM
Super User
Posts: 13,498

Re: Aggregate and Calculate by Jurisdiction and Overall

Maybe

```proc tabulate data=have;
class jurisdictn buildingtype;
var enr;
table  all jurisdictn='',
buildingtype='' * enr=''*mean=''*f=best6.;
run;```

All Replies
Super User
Posts: 23,663

Re: Aggregate and Calculate by Jurisdiction and Overall

Look at the ALL keyword in PROC TABULATE.

Frequent Contributor
Posts: 93

Re: Aggregate and Calculate by Jurisdiction and Overall

Plenty of ways to do this.  Here's one:

``````data have;
infile datalines missover;
informat buildingname \$10. buildingtype \$8. responded \$1. jurisdictn \$9. enr best5. ;
input buildingname 1-10  buildingtype  responded  jurisdictn  enr  ;
cards;
happy days private Y nottingha 100
learning   public  N nottingha
buildingbl private Y nottingha 55
montessori private N nottingha
bean stalk private Y brunswick 55
learning   private Y brunswick 22
early lear private Y brunswick 255
childtime  private N brunswick
risingstar public  N brunswick
doodlebugs public  Y brunswick 34
;

run;

proc means data=have mean;
class jurisdictn buildingtype;
var enr;
run;

proc tabulate data=have;
class jurisdictn buildingtype;
var enr;
table  jurisdictn,
buildingtype * enr*mean;
run;

PROC SQL;
CREATE TABLE REPORT AS
SELECT 'OVERALL' AS Jurisdiction
, SUM(CASE WHEN BUILDINGTYPE = 'private' THEN ENR END)/SUM(CASE WHEN BUILDINGTYPE = 'private' AND ENR IS NOT NULL THEN 1 ELSE 0 END) As Private
, SUM(CASE WHEN BUILDINGTYPE = 'public' THEN ENR END)/SUM(CASE WHEN BUILDINGTYPE = 'public' AND ENR IS NOT NULL THEN 1 ELSE 0 END) as Public
FROM HAVE
UNION ALL
SELECT DISTINCT 'Jurisdiction', ., . FROM HAVE
UNION ALL
SELECT jurisdictn
, SUM(CASE WHEN BUILDINGTYPE = 'private' THEN ENR END)/SUM(CASE WHEN BUILDINGTYPE = 'private' AND ENR IS NOT NULL THEN 1 ELSE 0 END) As Private
, SUM(CASE WHEN BUILDINGTYPE = 'public' THEN ENR END)/SUM(CASE WHEN BUILDINGTYPE = 'public' AND ENR IS NOT NULL THEN 1 ELSE 0 END) as Public
FROM HAVE
GROUP BY jurisdictn
;
QUIT;``````
Regular Contributor
Posts: 206

Re: Aggregate and Calculate by Jurisdiction and Overall

Thanks Everyone!

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: Table WORK.REPORT created, with 60 rows and 3 columns

Super User
Posts: 23,663

Re: Aggregate and Calculate by Jurisdiction and Overall

jcis7 wrote:

Thanks Everyone!

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

NOTE: Table WORK.REPORT created, with 60 rows and 3 columns

IMO I would correct this.

Frequent Contributor
Posts: 93

Re: Aggregate and Calculate by Jurisdiction and Overall

For the sake of posterity:

``````PROC SQL;
CREATE TABLE REPORT AS
SELECT 'OVERALL' AS Jurisdiction
, SUM(CASE WHEN BUILDINGTYPE = 'private' THEN ENR ELSE . END)/SUM(CASE WHEN BUILDINGTYPE = 'private' AND ENR IS NOT NULL THEN 1 ELSE 0 END) As Private
, SUM(CASE WHEN BUILDINGTYPE = 'public' THEN ENR ELSE .  END)/SUM(CASE WHEN BUILDINGTYPE = 'public' AND ENR IS NOT NULL THEN 1 ELSE 0 END) as Public
FROM HAVE
UNION ALL
SELECT DISTINCT 'Jurisdiction', ., . FROM HAVE
UNION ALL
SELECT jurisdictn
, SUM(CASE WHEN BUILDINGTYPE = 'private' THEN ENR ELSE .  END)/SUM(CASE WHEN BUILDINGTYPE = 'private' AND ENR IS NOT NULL THEN 1 ELSE 0 END) As Private
, SUM(CASE WHEN BUILDINGTYPE = 'public' THEN ENR ELSE .  END)/SUM(CASE WHEN BUILDINGTYPE = 'public' AND ENR IS NOT NULL THEN 1 ELSE 0 END) as Public
FROM HAVE
GROUP BY jurisdictn
;
QUIT;``````
Super User
Posts: 23,663

Re: Aggregate and Calculate by Jurisdiction and Overall

Also, I know this is sample data but your jurisdictions value is being truncated here so make sure to fix that in your actual data.

Solution
‎05-01-2017 08:20 PM
Super User
Posts: 13,498

Re: Aggregate and Calculate by Jurisdiction and Overall

Maybe

```proc tabulate data=have;
class jurisdictn buildingtype;
var enr;
table  all jurisdictn='',
buildingtype='' * enr=''*mean=''*f=best6.;
run;```
☑ This topic is solved.