topic Re: PROC FREQ in SAS Programming

PROC FREQ

ss171 — Thu, 31 Dec 2020 12:28:58 GMT

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

Re: PROC FREQ

Ksharp — Thu, 31 Dec 2020 14:07:11 GMT

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;

Re: PROC FREQ

ss171 — Thu, 31 Dec 2020 14:42:23 GMT

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

Re: PROC FREQ

ballardw — Thu, 31 Dec 2020 15:32:39 GMT

@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.

Re: PROC FREQ

ss171 — Thu, 31 Dec 2020 15:43:05 GMT

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

Re: PROC FREQ

Cynthia_sas — Thu, 31 Dec 2020 16:02:10 GMT

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

Re: PROC FREQ

ss171 — Fri, 01 Jan 2021 07:19:12 GMT

Thanks !!