Aggregate and Calculate by Jurisdiction and Overall

[jcis7]

   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!

[Reeza]

Look at the ALL keyword in PROC TABULATE. 

Also, your code was cut off in your copy and paste.

[thomp7050]

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;

[jcis7]

Thanks Everyone!

Question, for the proc sql procedure, do I need be concerned about this in the log:

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

[Reeza]

---

@jcis7 wrote:

Thanks Everyone!

Question, for the proc sql procedure, do I need be concerned about this in the log:

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. 

[thomp7050]

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;

[Reeza]

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.

[ballardw · Accepted solution]

Maybe

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