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

Hi,

 

I have the following data:

data have;
infile datalines missover;
input buildingname $10. buildingtype $8. responded $1. jurisdictn $9. enr 3 ;
cards;
happy days privateY nottingha 100
learning public N nottingha
buildingbl privateY nottingha 55
montessori privateN nottingha
bean stalk privateY brunswick 55
learning private Y brunswick 22
early lear privateY brunswick 66
childtime private N brunswick
risingstar public N brunswick
doodlebugs public Y brunswick 34
;

run;

 

I couldn't fingure out how to get the enrollment to show in the datatable using the infile statement

Here are the calculations I need to do -- summarize by jursidiction and buldingtype: sum of enrollment divided by total # that responded 'Y'
Calculations:

jurisdiction      private                    public
nottingham    (100+55)/2       
brunswick     (55+22+255)/3            34/1


Final results:
jursidiction     private    public
nottingham     77.5            
brunswick       110.7    34.0

 

 

 

 

How would I do that?  Thanks!  I'm on Base SAS 9.4

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

There are some obnoxious interactions that happen with formats on the INPUT statement and text with embedded blanks. Also pasting code into the main message window of the forum can cause some other issues with removing blanks and shifting columns. So it is hard to tell what your issue with infile may have been. Pasting code into a code box opened with the forum {i} menu icon is best to help with some of those issues.

the example below will read the enr as well as the building name for the given data.

You don't say whether you want a report or data set for a result.

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 66
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;

 

Options in the proc tabulate let you adjust appearance to remove/change column labels but this should get you started.

View solution in original post

7 REPLIES 7
ballardw
Super User

There are some obnoxious interactions that happen with formats on the INPUT statement and text with embedded blanks. Also pasting code into the main message window of the forum can cause some other issues with removing blanks and shifting columns. So it is hard to tell what your issue with infile may have been. Pasting code into a code box opened with the forum {i} menu icon is best to help with some of those issues.

the example below will read the enr as well as the building name for the given data.

You don't say whether you want a report or data set for a result.

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 66
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;

 

Options in the proc tabulate let you adjust appearance to remove/change column labels but this should get you started.

jcis7
Pyrite | Level 9
Appreciate your help. How do I export to Excel? Thanks.
ballardw
Super User

@jcis7 wrote:
Appreciate your help. How do I export to Excel? Thanks.

Responses may depend on exactly what you mean by "export to Excel".

If you want to end up with a semi-nice looking table then either ODS tagsets.excelxp or ODS Excel would work. Details differ but basically:

 

ODS Excel file="path/output.xlsx";

 

<Procedure code that generates output, may be many outputs>

ODS Excel close;

would send the output to the named file.

kiranv_
Rhodochrosite | Level 12

this should work

 

proc sql;

select jurisdictn ,

max(case when buildingtype='private' then sum_enr end) as private format =comma10.1,

max(case when buildingtype= 'public' then sum_enr end) as public format = comma10.1

from

(select jurisdictn, buildingtype, avg(enr) as sum_enr

from have

group by 1, 2)t

group by 1;

quit;

jcis7
Pyrite | Level 9
Thanks. What does this do and mean:


group by 1, 2)t


mkeintz
PROC Star

You have two topics with identical titles and content.

 

Please remove one of them.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
jcis7
Pyrite | Level 9
Thanks. I attempted to post a message and got an error message saying the was unable to process. So I copied what I had entered, closed the window, and opened a new window, pasted into the new window and posted. I would delete but have responses for both.

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
  • 1064 views
  • 2 likes
  • 4 in conversation