PROC FREQ

[ss171]

Hi Team , 

I need to customized a report in the attached manner . Please suggest way to do color format with proc freq cross tabulation

Below is the attached sample data 

data test;
input portfolio$ indicator$;
cards;
bank A
BANK N
RPL Y
OIL R
bank R
BANK Y
RPL A
OIL A
THD A
SEARS R
BANK R
MACYS A
MACYS R
;
RUN;

ods excel file="/risk/sbs/si/users/crm3e/reports/CRM_Other_Reports.xlsx" 
options ( sheet_name='Reports' sheet_interval='none' embedded_titles='yes');
title bold h=16pt f='ARIAL' color=BLACK 'Migration' ;
PROC FREQ DATA=TEST;
TABLES portfolio* indicator/nocol nopercent norow ;
RUN;

Required output is attached 

[Ksharp]

data test;
input portfolio $ indicator $;
cards;
bank A
BANK N
RPL Y
OIL R
bank R
BANK Y
RPL A
OIL A
THD A
SEARS R
BANK R
MACYS A
MACYS R
;
RUN;

proc tabulate data=test;
class portfolio indicator;
classlev portfolio/style=data;
table portfolio=' ' all*{style=header}, 
      indicator=' ' all/box='portfolio' misstext='0';
keylabel n=' ' all='Total';
run;

[ss171]

Thanks for the solution .
Can I change background color as well in the heading and Total results . I tried to use in class statement but it does not work there

[ballardw]

---

@ss171 wrote:
Thanks for the solution .
Can I change background color as well in the heading and Total results . I tried to use in class statement but it does not work there

---

Background colors where? Rules ?

In row or column headings this would typically be done in a CLASSLEV statement. If you tried that show the code you used and describe why that wasn't successful.

If in the body of the table then a style override for the specific statistic(s) might be involved, but need to know exactly where you want things colored.

[ss171]

Need to format column heading and last row where it represents Total in different color format image attached above . How can I modify code for that

[Cynthia_sas]

Hi:

  Yes, you'll have to use more style overrides ... not in the CLASS statement. I made each override a different color so you could see which overrides controlled which cells. If you want them all to be lightcyan in color, then you have to change each override.

proc tabulate data=test;
class portfolio indicator;
classlev portfolio/style=data{background=verylightgray};
classlev indicator / style={background=lightyellow};
table portfolio=' ' all*{style=header{background=lightgreen}}, 
      indicator=' ' all/ misstext='0'
      box={label='portfolio' style={background=lightred}};
keylabel n=' ' all='Total';
keyword all / style={background=lightcyan};
run;

OR, you could switch to PROC REPORT:

options missing=0;
proc report data=test 
  style(header)={background=lightcyan}
  style(summary)={background=lightcyan};
  column portfolio n,indicator n=tot;
  define portfolio / group 'Portfolio';
  define indicator / across ' ';
  define n / ' ';
  define tot / 'Total';
  rbreak after / summarize;
  compute after;
    portfolio = 'Total';
  endcomp;
run;

In the PROC REPORT, I made everything light cyan in color.

Cynthia

[ss171]

Thanks !!