Proc Report - Total

[RUC]

I have to sum up some of the selected variables in a proc report.
for e.g. there are 3 variables namely

app goals % to goal

so here if I have to summarize only app and goal...

Please advice for the syntax.
Thanks

[Cynthia_sas]

Hi:
Consider the following code:
[pre]
proc report data=sashelp.class nowd;
column age name sex height weight;
define age / order;
define name / order;
define sex / display;
define height / analysis sum;
define weight /analysis mean;
rbreak after /summarize;
run;
[/pre]

By default, without other instructions from you, all numeric variables in your dataset are treated as ANALYSIS variables with a statistic of SUM -- by PROC REPORT. So in the above code, I am turning off the ANALYSIS usage of AGE by using it as an ORDER item on the report. Then the HEIGHT variable is going to be used with the SUM statistic at any BREAK or RBREAK summary and the WEIGHT variable will be used with the MEAN statistic at any BREAK or RBREAK summary.

To turn off the usage of ANALYSIS, you have to use a different usage for a report item. usages of DISPLAY or ORDER or GROUP will "turn off" any summarizing for that report item. For example, if I wanted to turn off summarizing for HEIGHT, but leave summarizing for WEIGHT, I would need to change the DEFINE statement for HEIGHT:
[pre]
define height / display;
[/pre]

cynthia

[RUC]

Thanks Cynthia.....Also please advice if I want to print only the values of any variable without labels ..what will be the syntax for that.

for e.g. apps goals %to goal - here i have values like 10 20 50%

Now If I want the only the 10 20 50% on my excel and not the heads

like

Total 10 20 50%

please advice

[Cynthia_sas]

Hi:
I'm not exactly sure what you mean by "without any labels" -- do you mean column headings??? PROC REPORT has an option called NOHEADER that might do what you want:
[pre]
proc report data=sashelp.class nowd noheader;
...more code...
run;
[/pre]

Otherwise, if you want SOME headers, such as for NAME, but not others, such as for AGE and HEIGHT, then you would control that in the DEFINE statement:
[pre]
define name / display 'The Name';
define age / display ' ';
define height / display ' ';
[/pre]

In the above DEFINE statements, the quoted string is the column header to use. For example, NAME will have a column header of The Name, but the ' ' (quote space quote) will be used as the header for AGE and HEIGHT, effectively blanking out their labels in the header area.

cynthia

[RUC]

Thanks Cynthia..I was looking for this only and tried however i am getting double rows in this...like output is coming as

10 20 50%
10 20 50%

Also if you can please advice

Is it possible to have header in a row then in a column like

Total 10 20 50%

[Cynthia_sas]

Hi:
Using a COMPUTE block you can assign a label. See the code below:
[pre]
ods listing;
proc report data=sashelp.class nowd;
column name sex height weight;
define name / order;
define sex / display;
define height / analysis sum;
define weight /analysis mean;
rbreak after /summarize;
compute after;
name = 'Total';
endcomp;
run;
[/pre]

Without seeing your actual code, it is hard to comment on why you are getting dupped rows. I suspect you have a BREAK or RBREAK statement where you do not need one.

If you are unwillling to post code to the forum, it might be a better idea for you to work with Tech Support. There is no way to really answer your question without looking at your data going into PROC REPORT and the code that you are using.

cynthia

[RUC]

Hi Cynthia,

Thanks for your help till now however again got stuck....

I have to hit 2 different datasets as per my report requirement .

However I need all the output on same tab so for that I am using 2 proc reports

but due to this I am getting a colored line and 3 blank rows between 2 proc reports

So is there a way to remove the line and the between blank rows.

Please advice


Thanks
Ruc

[Cynthia_sas]

Hi:
As I said before, if you are unwilling to share your code, it is difficult to understand what you mean. For example, you say you want all the output "on the same tab"?? If you are using ODS HTML, then you would be getting HTML files, if you are using ODS PDF, then you would be getting a PDF file. I don't know what you mean by "tab". You might mean that you are creating output for Excel to open. In that case, it would be useful to know how you are creating your output -- PROC EXPORT?? ODS HTML?? ODS MSOFFICE2K?? ODS TAGSETS.EXCELXP?? Without code, there is no context for what you mean by "tab".

PROC REPORT will only deal with 1 dataset at a time. If you need data from 2 datasets -- and the datasets have the same variables (possibly you have 1 month's data in 1 dataset and another month's data in a second dataset), then you could "stack" both datasets together and use the month variable for BY group processing or PAGE processing in PROC REPORT.

When you say you are getting a "colored line and 3 blank rows" between your two PROC REPORTS, this sounds like the standard horizontal rule that is placed between tables when you use ODS HTML. That horizontal rule comes from the SAS style template. The way to get rid of it is to use a style template in your STYLE= option that does not have a horizontal rule. The ones I know about are: FESTIVAL, SEASIDE, MEADOW, PLATEAU.
[pre]
ods html file=.... STYLE=PLATEAU;
...more code...
ods html close;
[/pre]

However, even if you use a STYLE template to alter the horizontal rule behavior, you will still have space between each table. The code below creates 2 different datasets -- for different schools and then creates a single dataset to use with PROC REPORT. With the Plateau style, I find that there is less of a gap between tables using the PAGE option versus using the BY statement -- with ODS HTML. Since I don't know what destination you are using, I am only speculating that switching to the PLATEAU style might work for you.

Again, if you are unwilling to share your code and describe more about your data (what is in the 2 different datasets), then you will have to open a track with Tech Support to see if they can provide more assistance.

cynthia
[pre]
data onefile;
length school $10;
infile datalines;
input school $ name $ age $;
return;
datalines;
Ridgemont alan 16
Ridgemont bob 17
Ridgemont carla 16
;
run;

data otherfile;
length school $10;
infile datalines;
input school $ name $ age $;
return;
datalines;
Shermer xavier 16
Shermer york 17
Shermer zenobia 16
;
run;

data allschools;
set onefile
otherfile;
run;

proc sort data=allschools;
by school name;
run;

title; ods listing close;
ods html file='c:\temp\html_plateau1.html' style=plateau;

** PROC REPORT with PAGE option. No titles to show just space;
** between tables;
proc report data=allschools nowd;
column ('1) PROC REPORT with PAGE' school name age);
define school / order;
define name / order;
define age / display;
break after school / page;
run;
ods _all_ close;

ods html file='c:\temp\html_plateau2.html' style=plateau;

** PROC REPORT with BY option. No titles to show just space;
** between tables;
options nobyline;
proc report data=allschools nowd;
by school;
column ('2) PROC REPORT with BY groups' school name age);
define school / order;
define name / order;
define age / display;
run;
options byline;
title;
ods _all_ close;
[/pre]