The SAS Output Delivery System and reporting techniques

Change text color in PROC FREQ crosstabs

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Change text color in PROC FREQ crosstabs

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


Accepted Solutions
Highlighted
Solution
a week ago
Super User
Posts: 11,114

Re: Change text color in PROC FREQ crosstabs

[ Edited ]

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... Smiley Tongue

 

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.

View solution in original post


All Replies
Super User
Posts: 11,114

Re: Change text color in PROC FREQ crosstabs

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;
Contributor
Posts: 36

Re: Change text color in PROC FREQ crosstabs

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!!

SAS Super FREQ
Posts: 8,818

Re: Change text color in PROC FREQ crosstabs

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
Super User
Posts: 11,114

Re: Change text color in PROC FREQ crosstabs


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.

Contributor
Posts: 36

Re: Change text color in PROC FREQ crosstabs

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... Smiley Frustrated

 

Is there an easy way to do it?

 

Thanks again!! Smiley Happy

Super User
Posts: 11,114

Re: Change text color in PROC FREQ crosstabs

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.

Contributor
Posts: 36

Re: Change text color in PROC FREQ crosstabs

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... Smiley Tongue

 

Thanks again for your help.

Highlighted
Solution
a week ago
Super User
Posts: 11,114

Re: Change text color in PROC FREQ crosstabs

[ Edited ]

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... Smiley Tongue

 

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.

Contributor
Posts: 36

Re: Change text color in PROC FREQ crosstabs

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 240 views
  • 1 like
  • 3 in conversation