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
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.
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 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.
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;
You have two topics with identical titles and content.
Please remove one of them.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: