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!
Maybe
proc tabulate data=have; class jurisdictn buildingtype; var enr; table all jurisdictn='', buildingtype='' * enr=''*mean=''*f=best6.; run;
Look at the ALL keyword in PROC TABULATE.
Also, your code was cut off in your copy and paste.
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;
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
@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.
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;
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.
Maybe
proc tabulate data=have; class jurisdictn buildingtype; var enr; table all jurisdictn='', buildingtype='' * enr=''*mean=''*f=best6.; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.