BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9
   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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Maybe

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

View solution in original post

7 REPLIES 7
Reeza
Super User

Look at the ALL keyword in PROC TABULATE. 

 

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

thomp7050
Pyrite | Level 9

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
Pyrite | Level 9

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
Super User

@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
Pyrite | Level 9

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
Super User

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
Super User

Maybe

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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