Change text color in PROC FREQ crosstabs

[emera86]

Dear all,

For large cross tabulation tables generated with PROC FREQ I find it difficult to check the results.

I was thinking on changing all the frequency cells to one font/background color, the percents to a different ones and so on.

I have been trying then to modify the template of this procedure but I haven't been able to change the colors. I have been following this example to change the format of the results which I thought to be very similar to my color problem but I don't know which is the correct statement to change the font/background color inside the, for example, define frequency piece of code (I have been trying color, style, cellstyle, etc. but I'm doing something wrong):

proc format;
	picture pctfmt (round) other='009.9%';
run;

proc template;
	define crosstabs Base.Freq.CrossTabFreqs;
		cellvalue Frequency Expected Deviation CellChiSquare TotalPercent Percent RowPercent ColPercent CumColPercent;
		define Frequency;
			format=8.;
		end;
		define Percent;
			format=pctfmt.;
		end;
	end;
run;

Thank you all very much for your help in advance!!

Best regards

[ballardw]

You might consider using proc tabulate depending on exactly what our are needing. You can set a style override for each statistic requested. Of course tabulate does not stack values into a single cell so the colors may not be needed.

proc tabulate data=sashelp.class;
   class age sex;
   tables age,
          sex*(n      *{style=[color=black]}
                  pctn   *{style=[color=green]}
                  rowpctn*{style=[color=purple]}
                  colpctn*{style=[color=red]}
                 )
   ;
run;

[emera86]

What I'm looking for is to generate a simple crosstabulation table but muy variables have many levels, so the visualization of frequencies and percents per row or per column are difficult to check. Here is an example of what I'm doing with PROC FREQ:

proc freq data=test-dataset;
	table var1*var2;
run;

Is there any way to apply your PROC TABULATE approach to PROC FREQ?

Many thanks!!

[Cynthia_sas]

Hi, you would need to change the table template for PROC FREQ, and it might not give you the results you want. Using PROC TABULATE really is easier, in the long run, then changing the TABLE template.

cynthia

[ballardw]

---

@emera86 wrote:

What I'm looking for is to generate a simple crosstabulation table but muy variables have many levels, so the visualization of frequencies and percents per row or per column are difficult to check. Here is an example of what I'm doing with PROC FREQ:

proc freq data=test-dataset;
	table var1*var2;
run;

Is there any way to apply your PROC TABULATE approach to PROC FREQ?

Many thanks!!

---

Did you look at the output from the Proc Tabulate example I provided? It has each statistic in a separate column which is much easier to read in general than the stacked percentages that appear in Proc Freq. Replace the data set name with your data and var1 and var2 in the place of age and sex.

[emera86]

Dear @ballardw,

Yes I did, but I also need the totals for each column and row and the global one that are produced automatically with PROC FREQ and I don't know how to generate them with the PROC TABULATE... :S

Is there an easy way to do it?

Thanks again!! 🙂

[ballardw]

In proc tabulate you use the ALL to get row/column combined stats:

proc tabulate data=sashelp.class;
   class age sex;
   tables age all='All Ages',
          (sex all='All Sexes')*(n      *{style=[color=black]}
                  pctn   *{style=[color=green]}
                  rowpctn*{style=[color=purple]}
                  colpctn*{style=[color=red]}
                 )
   ;
run;

I'm not sure what you mean by the "the global one" unless you mean the intersection of the "all" columns in the example above that appear in the bottom right corner.

The all can appear before the variable to summarize as well so you could have the "all" in the first row or first column, your choice.

[emera86]

Dear @ballardw,

Thank you for your patience when dealing with SAS newbies.

Yes, that was what I meant with "global total". There's still a detail that I would need to change: I just need the N total, not the PctN, RowPctN or ColPctN. How can I remove this data from the total row/columns?

I promise this is all... 😛

Thanks again for your help.

[ballardw · Accepted solution]

---

@emera86 wrote:

Dear @ballardw,

Thank you for your patience when dealing with SAS newbies.

Yes, that was what I meant with "global total". There's still a detail that I would need to change: I just need the N total, not the PctN, RowPctN or ColPctN. How can I remove this data from the total row/columns?

I promise this is all... 😛

Thanks again for your help.

---

Remove those elements from the syntax. Or are you saying that you only want the n but not the percentages from the total rows?

There are reasons that the guidelines for good questions says to show an example of the desired output, and best to provide an example data set to work with if you want tested code. At this point I do not know what you actually want for output.

Proc tabulate will create all crosses of the variables so the All Ages row will have the percentages. We can drop them from the All Sexes column with:

proc tabulate data=sashelp.class;
   class age sex;
   tables age all='All Ages',
          sex *(n      *{style=[color=black]}
                  pctn   *{style=[color=green]}
                  rowpctn*{style=[color=purple]}
                  colpctn*{style=[color=red]}
                 )
         all='All Sexes'*n
   ;
run;

an approach would be possible to move the percentages into ROW  instead of Column statistics which would allow only calculating the All Ages n but that would result in the All Sexes having the percentages.

OR you can actually make an additional table with just the counts:

proc tabulate data=sashelp.class;
   class age sex;
   tables age all='All Ages',
          (sex  all='All Sexes')*(n      *{style=[color=black]}
                  pctn   *{style=[color=green]}
                  rowpctn*{style=[color=purple]}
                  colpctn*{style=[color=red]}
                 )
   ;
   tables age all='All Ages',
          sex all='All Sexes'
   ;
run;

Since you started by saying that you needed color to differentiate between reading variables for some sort of comparison I am not sure why having extra percentages cause a problem. When comparing two different outputs the structure would be the same and the comparison should be easy.

[emera86]

Dear @ballardw,

Sorry for being unclear. From the beggining I just wanted to somehow reproduce the same output that I get from the PROC FREQ so your solution fits perfectly. Thank you very much for your help! I will try to follow more closely the guidelines from now on...

Thanks again!