DATA Step, Macro, Functions and more

Aggregate and Calculate by Jurisdiction and Overall

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

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: 11,343

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Aggregate and Calculate by Jurisdiction and Overall

Look at the ALL keyword in PROC TABULATE. 

 

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

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: 194

Re: Aggregate and Calculate by Jurisdiction and Overall

Posted in reply to thomp7050

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

 

Super User
Posts: 19,770

Re: Aggregate and Calculate by Jurisdiction and Overall


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. 

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: 19,770

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: 11,343

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 212 views
  • 4 likes
  • 4 in conversation