topic Re: Aggregate and Calculate by Jurisdiction and Overall in SAS Programming

Aggregate and Calculate by Jurisdiction and Overall

jcis7 — Mon, 01 May 2017 20:57:41 GMT

   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!

Re: Aggregate and Calculate by Jurisdiction and Overall

Reeza — Mon, 01 May 2017 21:00:52 GMT

Look at the ALL keyword in PROC TABULATE.

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

Re: Aggregate and Calculate by Jurisdiction and Overall

thomp7050 — Mon, 01 May 2017 21:10:02 GMT

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;

Re: Aggregate and Calculate by Jurisdiction and Overall

Reeza — Mon, 01 May 2017 21:27:50 GMT

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.

Re: Aggregate and Calculate by Jurisdiction and Overall

ballardw — Mon, 01 May 2017 21:44:14 GMT

Maybe

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

Re: Aggregate and Calculate by Jurisdiction and Overall

jcis7 — Tue, 02 May 2017 00:38:22 GMT

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

Re: Aggregate and Calculate by Jurisdiction and Overall

Reeza — Tue, 02 May 2017 00:49:06 GMT

@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.

Re: Aggregate and Calculate by Jurisdiction and Overall

thomp7050 — Tue, 02 May 2017 12:25:03 GMT

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;