How to print a tablevalue in a header

[deleted_user]

In a proc report segment of my SP, I want to be able to include a value from a table in a report header. The data I am using has been groupd, and I have BREAK statements in my proc report code..... however, I haven't been able to include a table value in my header lines yet. Here is the code I am using:

%macro Report_Summary_A;

FOOTNOTE;
FOOTNOTE1 h=2 "Report Name: HFA931";
FOOTNOTE2 h=2 "Generated by ACCD Information Technologies on %SYSFUNC(DATE(), WEEKDATX23.) at %SYSFUNC(TIME(),TIMEAMPM8.)";

PROC REPORT data = byFieldsA spacing = 1 style(Header)={font_size=1.0 font_weight=medium} split='*' missing;

TITLE;
TITLE1 "ALAMO COMMUNITY COLLEGES";
TITLE2 h=4 " District Employee Summary Report for " Primary_campus;

column Primary_campus primary_position Primary_dept Primary_degree N /*counter*/;

define Primary_Campus /group noprint /*order order=internal group*/ format=$campus. "CAMPUS" style=[just=l font_weight=demi_bold font_Size=1.0];
define Primary_Position /group /*order order=internal group */ "POSITION" style=[just=l font_weight=demi_bold font_Size=1.0];
define Primary_Dept /group /*order order=internal group*/ "DEPARTMENT" style=[just=l font_weight=demi_bold font_Size=1.0];
define Primary_Degree /group /*order order=internal*/ "DEGREE" style=[just=l font_weight=demi_bold font_Size=1.0];
define N / "STAFF*COUNT" style=[just=l font_weight=demi_bold font_Size=1.0];

break before Primary_Campus / page ;

break after Primary_Campus/summarize style={background=darkgray FONT_WEIGHT=BOLD};

compute after Primary_Campus;
Primary_Position = "Total for " || Primary_Campus || " " || N;
endcomp;

break after Primary_Position/summarize style={background=darkgray FONT_WEIGHT=BOLD};

compute after Primary_Position;
Primary_Position = "Total " || Primary_Position || " " || N;
endcomp;

rbreak after / summarize style={background=darkgray FONT_WEIGHT=BOLD};

compute after;
Primary_Position = "Grand Totals " || N;
endcomp;

run;
%mend Report_Summary_A;

Thanks so very much!

[Cynthia_sas]

Hi:
Is this title statement where you want to use information from the report?
[pre]
TITLE2 h=4 " District Employee Summary Report for " Primary_campus;
[/pre]
or do you mean something else? I dont' see a COMPUTE BEFORE in your code. Although I see that you do have a BREAK BEFORE statement. So you could do this:
[pre]
compute before Primary_Campus;
line "This campus is: " Primary_Campus $40.;
endcomp;
[/pre]

But, remember that Web Report Studio does not like LINEs in PROC REPORT code. (Although this code would work in other client applications -- just not WRS.)

Think of your report as being a box. Inside the box, you can refer to Primary_campus (your variable). Outside the box (your report), the SAS TITLE statement does not know or care which data set and variables you have inside the "report box".

There is one way to use a data set variable in the SAS TITLE statement: that is using BY group processing and #BYVAR/#BYVAL in the title, which we have posted back and forth about previously. I understand from Tech Support that some stored processes with #BYVAR/#BYVAL do not work in Web Report Studio, so you may not be able to use this technique.

There is an alternate way to use a data set variable in your SAS title and that is to turn the data set variable into a SAS Macro variable -- BEFORE the TITLE statement is issued. Then you could use the macro variable anywhere in the code you wanted. Once a procedure starts, however, you could not take a variable that was being used IN the procedure and turn it into a macro variable for the TITLE statement. This technique might mean that you had to change your program code slightly.

If we move into the world of stored processes and you are, perhaps, prompting the end user for PRIMARY_CAMPUS , then you might already have a macro variable that you could use in a title.

I suggest you contact Tech Support for help, as what you want to do seems very specific to your stored process, whether you can make a macro variable out of PRIMARY_CAMPUS and how to do that in yoru macro and then in your stored process.

cynthia

[deleted_user]

Thanks, Cynthia for your informative reply!

Question: I did try using the #BYVAL logic, associated with the appropriate BY statement; when I tried that, my GRAND TOTALS line showed up on every singe BREAK summary line. How can I prevent that from happening when using #BYVAL? The reason I ask is that when I tried the #BYVAL, that was the closest to producing what I want.

Thanks again and again for your replies... they are very helpful.

[Cynthia_sas]

Hi,
The confusing thing, I think, is that using a BY statement in PROC REPORT frequently changes how your total lines appear or are calculated. That's because using a BY statement causes SAS to treat each BY group like a separate entity (many SAS procedures work this way -- not just PROC REPORT). So in those instances, with a BY statement (BY MY_BY_VAR), both BREAK AFTER MY_BY_VAR and RBREAK AFTER will give you the same number.

For example, this code
[pre]

options nocenter nodate nonumber nobyline;
proc sort data=sashelp.shoes out=shoes;
by product region;
run;

proc report data=shoes nowd;
title 'For Product: #byval(product)';
by product;
where region in ('Asia', 'Canada', 'Pacific');
column product region sales;
define product /group noprint;
define region /group;
define sales /sum;
break after product / summarize;
rbreak after / summarize;
compute after product ;
region = 'Break Stmt';
endcomp;
compute after ;
region = 'Rbreak Stmt';
endcomp;
run;
[/pre]

produces this output (only 2 BY groups shown) (Because of the way I customized the break lines, you can see which line is coming from the Break versus the Rbreak statement):
[pre]
For Product: Sandal

Region Total Sales
Asia $8,208
Canada $14,798
Pacific $48,424
Break Stmt $71,430
Rbreak Stmt $71,430

********************************* next by group

For Product: Slipper

Region Total Sales
Asia $152,032
Canada $952,751
Pacific $390,740
Break Stmt $1,495,523
Rbreak Stmt $1,495,523
[/pre]

So the solution, if you're going to use BY groups in order to get #BYVAL capability in the title, is to use EITHER BREAK AFTER your BY variable or RBREAK AFTER, but not both (speaking of the BY variable only here). If you experiment with the above code, you should then be able to figure out how your program has to change. Otherwise, you might consider contacting Tech Support for more PROC REPORT help.

cynthia

[deleted_user]

Hi, Cynthia,

Apologies for not replying sooner.... got swamped. Anyway, so you can't use a BREAK AFTER and a RBREAK AFTER at those times when you are using a #BYVAL..... interesting and frustrating. Especially when trying to get subtotals and grand totals in the same report.

Thanks for your reply.....as always, very appreciated!

[Cynthia_sas]

The side-effect of BY group processing is that every BY group gets treated as a whole entity -- so, every BY group's RBREAK is the same as the BREAK. Proc Tabulate does the same thing, for example, if you do by group processing with PROC TABULATE, then you can only get percents for each BY group, not across BY groups as a percent of ALL by groups.

One way around it is to just have another PROC REPORT with different variables in the column statement. Another way around it is to use Macro processing to put the variable of interest in the SAS title.

Here's an example of the first method. The first PROC REPORT is the same as that posted previously. The second PROC REPORT just drops PRODUCT from the column statement, thus making a table for ALL products and the RBREAK after this table gives you a separate grand total. If, however, you wanted a Grand Total by itself, then the 3rd PROC REPORT shows how to do that. When paging through the output, they wouldn't even know that multiple PROC REPORTs were used.

cynthia
[pre]
** alternate approach;
ods listing;
options nocenter nodate nonumber nobyline;

proc sort data=sashelp.shoes out=shoes;
by product region;
run;

proc report data=shoes nowd;
title 'Alternate Approach For Product: #byval(product)';
by product;
where region in ('Asia', 'Canada', 'Pacific');
column product region sales;
define product /group noprint;
define region /group;
define sales /sum;
break after product / summarize;
compute after product ;
region = 'Break Stmt';
endcomp;
run;

proc report data=shoes nowd;
title 'Alternate Approach For ALL Products';
where region in ('Asia', 'Canada', 'Pacific');
column region sales;
define region /group;
define sales /sum;
rbreak after / summarize;
compute after ;
region = 'Grand Total';
endcomp;
run;

** you may or may not use this code if the Grand Total;
** above is sufficient;
data shoes2;
set shoes;
length cat $35;
cat = 'All Regions, All Products';
run;

proc report data=shoes2 nowd;
title 'Alternate Approach For ALL Products and ALL Regions';
where region in ('Asia', 'Canada', 'Pacific');
column cat sales;
define cat /group ' ';
define sales /sum;
run;
[/pre]