The SAS Output Delivery System and reporting techniques

Proc Report -Question

Reply
SAS Employee
Posts: 105

Proc Report -Question

Hi,
I build a proc report and i tried to create a style on a specific column (_c4_).
The problem is that i want the style to apply to the column heading as well (and not only to the cells)

a short example to my proc report:

proc report data=pol_10 nowindows split='|' out=a;
columns Ezor_c Hoze ,(Stscbt By_zone) Commerical;
define Ezor_c/'zone' group;
define Hoze/across format=hoze_label.;
define Stscbt/'Stscbt' sum format=comma20.;
define By_zone/'%|By Zone ' computed format=percent15.2;
define Commerical/'100%|Commerical ' format=comma20. ;
rbreak after / summarize ;
compute after;
Ezor_c='TOTAL';
if _break_ = '_RBREAK_' then do;
call define(_row_,'style','style={font_weight=bold}');
end;
endcomp;

compute By_zone;
_c3_=_c2_/&sum_207;
_c5_=_c4_/&sum_503;
*the style apply only to the cells;
call define('_c4_', "style", "style={background=gray}" );
endcomp;

compute Commerical/ ;
Commerical=sum(_c2_,_c4_);
endcomp;
quit;

Could you please guide me?
have a wonderful holiday season and happy new year.
Super Contributor
Posts: 273

Re: Proc Report -Question

Normally

[pre]
define columname /... style(header)={anystyle order} ;
and
define columname / ... style(column)={ ... };
[/pre]

solve your column inquiry;
Andre
SAS Super FREQ
Posts: 8,868

Re: Proc Report -Question

Hi:
As Andre suggests, you need to set the style of the header cells in a different place than the CALL DEFINE statement. You can achieve a different color for particular header cells by using a user-defined format, as shown below.

cynthia
[pre]
proc format;
value $reg 'Asia' = 'pink'
'Canada' = 'yellow'
'Pacific' = 'cxcccccc';
run;

ods html file='c:\temp\across_style.html' style=sasweb;

proc report data=sashelp.shoes nowd
style(header)={foreground=black};
where region in ('Asia', 'Canada', 'Pacific');
column product sales,region sales=alltot;
define product / group;
define sales / sum ' ';
define region / across
style(header)={background=$reg.};
define alltot / sum 'All Regions Total';
compute region;
call define('_c2_', 'style','style={background=pink}');
call define('_c3_', 'style','style={background=yellow}');
call define('_c4_', 'style','style={background=cxcccccc}');
endcomp;
run;

ods html close;
[/pre]
SAS Employee
Posts: 105

Re: Proc Report -Question

Hi Andre and Cynthia,
Thanks alot, I appreciate your help!!
SAS Employee
Posts: 105

Re: Proc Report -Question

Hi,
I still have the same problem:
_c_4 is a part of Stscbt column
If i will put the style in the call define statment _c_2 will also be part of the style....

I only want _c_4 will be styled.

is it possible?

Thainks in advanced
SAS Super FREQ
Posts: 8,868

Re: Proc Report -Question

Hi:
The correct absolute column reference is _cn_ (_c2_, _c3_, _c4_) as shown in my code. (Not _c_4, as you have shown.) If you are using that incorrect form of the absolute column reference in your code, you should be seeing this note in the Log:
[pre]
NOTE: Variable _c_4 is uninitialized.
[/pre]

If you have two items under an across variable, each item gets a unique absolute column number. When you use that unique column number in a CALL DEFINE statement, you can only "touch" the DATA cells for that column. I interpreted your original post as saying that you wanted the DATA cells under an ACROSS variable and the HEADER cells for the ACROSS variable to have the same style.

If you compare my code with your code, you will see that I blanked out the labels for the SALES variable -- because my format only "touched" the header cells for Asia, Canada and Pacific regions. If I had coded my column statement differently and had used a header string for Sales, :
[pre]
column product region,sales sales=alltot;
define product / group;
define region / across
style(header)={background=$reg.};
define sales / sum 'Sales';
[/pre]

(with the rest of the program unchanged), then you would see that each Header for Sales would be unaffected by the change to the ACROSS header above it. This is true because you have a DEFINE Statement for the ACROSS variable, where you can touch the header of the ACROSS variable, but you only have 1 DEFINE statement for the SALES variable (or for each variable under the ACROSS variable) and there's no CALL DEFINE way to change the header of the item under an ACROSS variable.

I -think- this is what you mean when you say that _c4_ is part of STSCBT column, because your original program had:
[pre]
proc report data=pol_10 nowindows split='|' out=a;
columns Ezor_c Hoze ,(Stscbt By_zone) Commerical;
define Ezor_c/'zone' group;
define Hoze/across format=hoze_label.;
define Stscbt/'Stscbt' sum format=comma20.;
define By_zone/'%|By Zone ' computed format=percent15.2;
[/pre]

where you have assigned labels to STSCBT and your calculated BY_ZONE. So, if you use a user-defined format to change the color of the HOZE variable, and you use CALL DEFINE to change the color of the STSCBT and BY_ZONE columns, the headers for STSCBT and BY_ZONE will use the header style from ODS. Is this what you're seeing???

You will probably need to use a different approach in order to get each STSCBT and BY_ZONE header to be the same color as the columns underneath them. So, for example, in the scenario below _c2_ and _c3_ are under the first value for HOZE and _c4_ and _c5_ are under the second value for HOZE and_c6_ and _c7_ are under the third value for HOZE...like this (let's assume that you have 3 values for the HOZE variable, HOZE1, HOZE2 and HOZE3):
[pre]
------HOZE1-------- ------HOZE2-------- ------HOZE3--------
Ezor_c STSCBT1 BY_ZONE1 STSCBT2 BY_ZONE2 STSCBT3 BY_ZONE3 Commercial
abs name: _c2_ _c3_ _c4_ _c5_ _c6_ _c7_
[/pre]

So when you say you ONLY want _c4_ to be styled --you could want to change the style for:
1) only HEADER cell for _c4_
2) only the DATA cells for _c4_
3) BOTH the HEADER cells and DATA cells for _c4_
4) _c4_ is UNDER HOZE2 -- so what impact or change should there be to the header for HOZE2??
a) HEADER for HOZE2 is same as from the ODS style
b) HEADER for HOZE2 should be the same as the _c4_ color

Can you clarify what you mean when you say "If i will put the style in the call define statment _c2_ will also be part of the style....I only want _c4_ will be styled" -- I don't understand this. I have already posted code that shows how the call define ONLY changes one absolute column. The CALL DEFINE for _c4_ should not have an impact on the datacells for _c2_. In my first program, the CALL DEFINE ONLY touched the "data" cells. In my program, I used a user-defined format to touch the header cells of the ACROSS variables. Also, are you sure you used _c4_ in the CALL DEFINE statement and not _COL_???

Thanks,
cynthia
SAS Employee
Posts: 105

Re: Proc Report -Question

Hi Cynthia,
First i I'm sorry for my inaccuracy,English is not my native language.
Thanks for your help and attention!.
I know _c_4 is not my absolute column reference...

i will take your example:

i want get each STSCBT and BY_ZONE for the HOZE variable, HOZE1, HOZE2 ):

------HOZE1-------- ------HOZE2-------- Ezor_c STSCBT1 BY_ZONE1 STSCBT2 BY_ZONE2 commrical name
_c2_ _c3_ _c4_ _c5_

i want to get BOTH the HEADER cells and DATA cells for _c4_
To get th data cells i use the call define statment:

call define('_c4_', "style", "style={background=gray}" );

it works ok!

but i dont know how to get the style for the header.

if i will use in the define statment style(header) options:
define Stscbt/'Stscbt' sum format=comma20. style(header)={background=gray};
each STSCBT (STSCBT1 ,STSCBT2 ,STSCBT3) will be styled and i want only
STSCBT2 (_c_4 will be styled)....

Message was edited by: yonib
SAS Super FREQ
Posts: 8,868

Re: Proc Report -Question

Hi:
I think the easiest and/or quickest way to make this work is to "flatten" your dataset a bit, so that you have a unique column for every STSCBT and BY_ZONE. PROC REPORT will do this quite nicely with the OUT= option -- and with a RENAME option, you can rename the _c2_, _c3_ columns to meaningful names. That way you will be able to control only the headers for _c4_ under HOZE2, without needing to change all the headers for STSCBT.

The program below uses PROC REPORT to create a "flattened" output dataset from the original data and then uses the "flattened" data for the final report. In my example, every set of headers (for Asia, Canada and Pacific) are treated differently, so you can see different ways to impact the headers and the data cells. I think you are saying that you want to have your report look like the Canada group on my report

cynthia
[pre]
** close any EG destinations that might be open;
ods _all_ close;

** or make sure that LISTING destination is closed;
** because you do not really care about seeing the output from;
** this first PROC REPORT. If you do want to see this output, then,
** turn ODS LISTING back on.;
** ods listing close;

** set options for PROC REPORT and PROC PRINT;
options nocenter ls=200 nodate nonumber;

** Use PROC REPORT to create an output dataset in a "flattened" structure;
proc report data=sashelp.shoes nowd
out=work.abscol(rename=(_c2_=Asum _c3_=Acalc _c4_=Aname
_c5_=Csum _c6_=Ccalc _c7_=Cname
_c8_=Psum _c9_=Pcalc _c10_=Pname));
title 'Use PROC REPORT for calculations and to build output dataset';
title2 'SALES is from the dataset, CALCVAR is my computed item, RNAME will hold the region name';
where region in ('Asia', 'Canada', 'Pacific');
column product region,(sales calcvar rname) ;
define product / group;
define region / across;
define sales / sum 'Sales' f=dollar12.;
define calcvar / computed 'CalcVar' f=dollar12.;
compute calcvar;
** A silly calculated variable;
_c3_ = sum(_c2_, 1000000);
_c6_ = sum(_c5_, 2000000);
_c9_ = sum(_c8_, 3000000);
endcomp;
compute rname / character length=25;
_c4_ = 'Asia';
_c7_ = 'Canada';
_c10_ = 'Pacific';
endcomp;
run;

** show output dataset created in above step;
ods listing;
proc print data=work.abscol;
title 'dataset created by proc report -- will need to use THESE variable names';
title2 'in the NEW proc report';
run;

** now use the dataset and new "flattened" columns in a new PROC REPORT step;
options center;
ods listing close;
ods html file='c:\temp\style_after_flat.html' style=sasweb;

proc report data=work.abscol nowd
style(header)={foreground=black};
title 'Now use new dataset with PROC REPORT';
title2 'Asia, Canada & Pacific columns are different to show how to do it';
column product Aname,(Asum Acalc) Cname,(Csum Ccalc) Pname,(Psum Pcalc) ;
define product / group;
define Aname /across ' '
style(header)={background=pink}
style(column)={background=pink};
define Asum / sum 'Sales' f=dollar14.
style(header)={background=pink}
style(column)={background=pink};
define ACalc / sum 'CalcVar' f=dollar14.
style(header)={background=pink}
style(column)={background=pink};
define Cname /across ' ' ;
define Csum / sum 'Sales' f=dollar14.
style(header)={background=yellow}
style(column)={background=yellow};
define CCalc / sum 'CalcVar' f=dollar14.;;
define Pname /across ' '
style(header)={background=green foreground=white};
define Psum / sum 'Sales' f=dollar14.
style(header)={background=cxcccccc}
style(column)={background=cxcccccc};
define PCalc / sum 'CalcVar' f=dollar14.
style(header)={background=cyan}
style(column)={background=cyan};
run;

ods html close;

title;
ods listing;

[/pre]
SAS Employee
Posts: 105

Re: Proc Report -Question

Posted in reply to Cynthia_sas
Hi Cynthia,
That's exactly what I wanted to do !!!!
Thanks alot for your help Smiley HappySmiley HappySmiley Happy
Ask a Question
Discussion stats
  • 8 replies
  • 392 views
  • 0 likes
  • 3 in conversation