topic Re: proc report in SAS Enterprise Guide

proc report

deleted_user — Fri, 10 Aug 2007 18:42:30 GMT

Hi,

My report is about Full-time employees, need how many females, males and ethnic background, and total for department. I am doing Proc report to do.

PROC REPORT data = sfile1 spacing = 1 style(Header)={font_size=2.0 font_weight=medium} split='*' missing;
column Department Gender N PRIMARY_ETHNICITY PRIMARY_ETHNICITY_DESC ;

define Department /group "DEPARTMENT" ;
define Gender /group "GENDER" ;
define PRIMARY_ETHNICITY_DESC /group "ETHNICITY" ;
define PRIMARY_ETHNICITY / noprint;
break after Gender / summarize;
compute after gender;
if gender = 'F' then
line "Total Females " N;

if gender ='M' then
line "Total Males " N;
endcomp;
break after PRIMARY_ETHNICITY / summarize;
compute after PRIMARY_ETHNICITY ;
if PRIMARY_ETHNICITY = '1' and Gender = 'F' then
line "Total Female Whites " N;
if PRIMARY_ETHNICITY ='2' and Gender = 'F' then
line "Total Female Blacks " N;
if PRIMARY_ETHNICITY ='3' and Gender = 'F' then
line "Total Female Hispanics " N;
endcomp;

break after department / summarize;
compute after department;
department="Total In "||Department;
line "total emp: " N;
endcomp;

break after department /summarize;
Rbreak after / summarize;
compute after;
line "Total : " N;
endcomp;

Please give me suggestions

I like to get the output as follows:

department Gender Ethnicity
CHEM F White
F Black
F Hispanic
Total Females 3
Total Whites 1
Total Blacks 1
Totla Hispanic 1
same for Males also

Thank you

Re: proc report

Cynthia_sas — Sat, 11 Aug 2007 18:39:02 GMT

Hi:
I am confused by what you want to do. It seems to me from your final example, that you want a DETAIL report with summary information at the bottom of the detail report. The report you want is is not what you would get with your PROC REPORT code. And, by now, you have discovered, the N statistic is not working the way you envision. I find it useful sometimes to step back and define what I'm about to explain -- what I mean by DETAIL and SUMMARY reports.

SAS is capable of producing both DETAIL and SUMMARY reports:
--DETAIL reports are reports which show one row on the finished report for EVERY observation in the data set (or subset) that I am using as input for the report.
--DETAIL reports CAN have summary lines -- subtotal and/or grandtotals and how you get the subtotals or grandtotals will depend on the procedure or technique you use to product the detail report.
--If I write a simple DETAIL report using SASHELP.CLASS -- then I will have 19 rows on the report that come from data -- because there are 19 records or observations in SASHELP.CLASS.
--SASHELP.CLASS doesn't lend itself to having subtotals or grandtotals, but let's suppose that I wanted to add up everybody's weight to see how heavy the students were -- I could add a subtotal line that would appear at the break between genders -- so I would know how much all the boys weighed and how much all the girls weighed. I could also add a grandtotal line that would appear at the bottom of the report to show how much all the students weighed. This is such a detail report:
[pre]
PROC PRINT DETAIL REPORT

Sex Name Age Height Weight

F Alice 13 56.5 84.0
Barbara 13 65.3 98.0
Carol 14 62.8 102.5
Jane 12 59.8 84.5
Janet 15 62.5 112.5
Joyce 11 51.3 50.5
Judy 14 64.3 90.0
Louise 12 56.3 77.0
Mary 15 66.5 112.0
--- ------
F 811.0

M Alfred 14 69.0 112.5
Henry 14 63.5 102.5
James 12 57.3 83.0
Jeffrey 13 62.5 84.0
John 12 59.0 99.5
Philip 16 72.0 150.0
Robert 12 64.8 128.0
Ronald 15 67.0 133.0
Thomas 11 57.5 85.0
William 15 66.5 112.0
--- ------
M 1089.5
======
1900.5
[/pre]

You can see that I have 1 report row for every student, plus there is added subtotal information and the final 1900.5 is the total weight of all the students.
The above DETAIL report with subtotals and grandtotals was produced with PROC PRINT. PROC PRINT only produces DETAIL reports -- where you have 1 report row for every observation in your dataset. PROC REPORT and the DATA step also produce detail reports. An example of the above report produced with PROC REPORT is shown below:
[pre]
PROC REPORT DETAIL REPORT

N
Gender Name Weight Count
Females Alice 84 1
Barbara 98 1
Carol 102.5 1
Jane 84.5 1
Janet 112.5 1
Joyce 50.5 1
Judy 90 1
Louise 77 1
Mary 112 1
------------ --------- ---------
Females 811 9
------------ --------- ---------
Males Alfred 112.5 1
Henry 102.5 1
James 83 1
Jeffrey 84 1
John 99.5 1
Philip 150 1
Robert 128 1
Ronald 133 1
Thomas 85 1
William 112 1
------------ --------- ---------
Males 1089.5 10
------------ --------- ---------
Total Weight 1900.5 19
[/pre]

One important thing to note about PROC REPORT is that by default, when you have a BREAK happening (such as the break between Females and Males), then PROC REPORT wants to put that information when the break occurs. In the above report, it does not make sense to ask for the N statistic, because N is 1 on every report line (with a name) and on the summary line, shows the count of female rows or male rows or all rows. And another thing about PROC REPORT is that once the report row has been written (such as the summary line for Females) the N statistic is "reset" so it can accurately reflect the count of Males and the total count. So by the end of the report, the value for N, at the report break will ONLY and ALWAYS be 19.

--SUMMARY reports are reports where ONE report row represents the consolidation of information or summary of information for some group. So, for example, if I had this report:
[pre]
PROC REPORT SUMMARY REPORT

Count of
Gender Weight Students
Females 811 9
Males 1089.5 10
============ ========= =========
Total Weight 1900.5 19
============ ========= =========
[/pre]
The above report would be considered a SUMMARY report -- The report line for females represents the summing up of all the rows for females and the report line for males represents the summing up of all the rows for males from the input data set.

While many of the SAS procedures will do SUMMARY reports (such as PROC FREQ and/or PROC MEANS), the "reporting" procedures that could produce the above report or something similar would be PROC REPORT and PROC TABULATE. In addition, the DATA step program could produce a report such as that shown above. (And I'm sure that somebody who's a wizard with SQL could probably do it in SQL, too.)

Here's an example of the above report done with PROC TABULATE:
[pre]
PROC TABULATE SUMMARY REPORT

+------------------------+
| |Weight | N |
+------------+-------+---|
|Sex | | |
+------------| | |
|Females | 811.0| 9|
+------------+-------+---|
|Males | 1089.5| 10|
+------------+-------+---|
|Total Weight| 1900.5| 19|
+------------+-------+---+

[/pre]
(All these reports are cut and pasted from the LISTING window, they would look much different in ODS HTML, RTF or PDF form.)

As I said at the beginning, what you want to do looks like a DETAIL report with summary information after each "group". So, if I extrapolate from your example you want this:
[pre]
Department Gender Ethnicity
CHEM F White
F Black
F Hispanic
Total Females 3
Total Whites 1
Total Blacks 1
Total Hispanic 1

CHEM M White
M White
M Black
M Black
M Black
M Hispanic
Total Males 6
Total Whites 2
Total Blacks 3
Total Hispanic 1

CHEM
Total Employees 9
Total Females 3
Total Males 6
Total Whites 3
Total Blacks 4
Total Hispanic 2
[/pre]

... or something like it. I guess I have conflicting ideas about this report -- for small detail reports, I see how this could be useful. But once the employee list gets to be more than 10 people, I wonder whether folks will really want to wade through all the detail lines to get to the "bottom line" or whether they might prefer to just see the summary information for each department and gender group.

It is possible to get the report you want. In my experience, it's probably easier to do the above example report with a DATA step program instead of PROC REPORT. Or you might consider separate reports -- one DETAIL report -- with all the employee details sorted by department, gender and ethnicity, but immediately followed by the summary information. If you are going to use PROC REPORT or DATA step program, you'd have to put that code into a code node in EG, so you could essentially get both your detail and summary reports in 1 HTML file.

If you review the above examples and consider what your report users really want to see -- do they want to see the summary lines interspersed with ALL the detail information (even if there are 100 employees) or do you think they would be happier with a DETAIL report that listed everybody, followed by a SUMMARY report that provided all the "slicing and dicing" summary information???

cynthia

Re: proc report

deleted_user — Mon, 13 Aug 2007 13:31:28 GMT

I want my report should be DETAIL report with summary information after each group. EX:
CHEM Instructor Black F
Professor Black M
Instructor Hisp F
Total Males: 1 Females: 2
Whites 0 0
Blacks 1 1
Hisp 0 1
Asians 0 0

MATH INSTRUCTOR White M

and so on.

Thank you
Raji
I would like to get the results for summary info for each group

Re: proc report

Cynthia_sas — Mon, 13 Aug 2007 19:10:53 GMT

Hi:
There are several different ways to do the report. If I had to pick between PROC REPORT and DATA step program, I guess I'd pick a DATA step program, because it allows me to set counting variables AND produce the report with one pass through the data. Even in PROC REPORT you have to set up counting variables to make this kind of report work, with so many summary lines after the detail section.

So, using SASHELP.CLASS, I can produce this report with a DATA step program.
[pre]
Data Step Detail Report With Summary Info

Name Gender Height Age

Alfred M 69.0 14
Alice F 56.5 13
Barbara F 65.3 13
Carol F 62.8 14
Henry M 63.5 14
James M 57.3 12
Jane F 59.8 12
Janet F 62.5 15
Jeffrey M 62.5 13
John M 59.0 12
Joyce F 51.3 11
Judy F 64.3 14
Louise F 56.3 12
Mary F 66.5 15
Philip M 72.0 16
Robert M 64.8 12
Ronald M 67.0 15
Thomas M 57.5 11
William M 66.5 15
Total Females: 9
Total Males: 10
Tall Students: 12
Short Students: 7

[/pre]
The program used to generate this output is shown at the bottom of this post.

For help with the programming logic of the program or for help tailoring the program to your data, your best bet is to contact Tech Support. You will need to use BY group processing and FIRST.byvar or LAST.byvar in order to correctly generate the report BY DEPARTMENT, for example.

cynthia
[pre]

ods html file='c:\temp\detail_with_summary_data.html' style=egdefault;
title 'Data Step Detail Report With Summary Info';
title2 ' ';
options missing = ' ' nodate nonumber;

** start the programming environment to calculate the counters;
** that I want to show;
data _null_;
** reset the length of Name so it can hold the summary line;
length Name $30;
set sashelp.class end=no_more_data;
** retain counters;
retain fcnt mcnt tcnt scnt 0;
** increment counters for each record;
if sex = 'M' then mcnt + 1;
else if sex = 'F' then fcnt + 1;
if height ge 60 then tcnt + 1;
else if height lt 60 then scnt + 1;
** start sending the report to ODS;
file print ods=(variables=(name sex height age));
** write the 4 columns listed in the variables= option;
** This will write out EVERY data row.;
put _ods_;
if no_more_data then do;
** at the end of the file "reset" the value for name;
** so it holds the summary line info;
** then write out the name to column 1;
** Note, Name field had to be bigger than the "old" name field;
** to hold the string that I wanted for the summary line.;
name = 'Total Females: '|| put(fcnt,3.0);
put @1 name ;
name = 'Total Males: '|| put(mcnt,3.0);
put @1 name ;
name = 'Tall Students: '|| put(tcnt,3.0);
put @1 name;
name = 'Short Students: '|| put(scnt,3.0);
put @1 name ;
end;
format age 3.0 height 6.1 sex $6.;
label sex = 'Gender';
run;
ods html close;
[/pre]