add statistics to proc freq output

[marc11235]

Simple question, here is code :

data status;
input lines_group $ rows_group $ value proportion;
datalines;
0$-15$ 0%-40% 10 30
26$-35$ 41%-60% 34 41
16$-25$ 41%-60% 22 51
26$-35$ 41%-60% 26 54
16$-25$ 61%-100% 17 90
26$-35$ 0%-40% 32 10
26$-35$ 61%-100% 30 78
26$-35$ 41%-60% 26 45
16$-25$ 0%-40% 22 12
;
run;
proc freq order=data;
tables lines_group*rows_group;
exact or;
run;

For 10k observations and 10X10 classes table, is it possible to add variables "value" and "proportion" means, with which the groups are defined? It would make 6 stats by cells. Maybe with another procedure than proc freq?  

Thanks, and have a great day!

Marc

[sbxkoenk]

PROC MEANS !

data status;
input lines_group $ rows_group $ value proportion;
datalines;
0$-15$  0%-40%   10 30
26$-35$ 41%-60%  34 41
16$-25$ 41%-60%  22 51
26$-35$ 41%-60%  26 54
16$-25$ 61%-100% 17 90
26$-35$ 0%-40%   32 10
26$-35$ 61%-100% 30 78
26$-35$ 41%-60%  26 45
16$-25$ 0%-40%   22 12
;
run;
proc freq order=data;
tables lines_group*rows_group;
*exact or;
run;
proc means nway completetypes;
 CLASS lines_group rows_group;
 VAR   value proportion;
run;
/* end of program */

Koen

[marc11235]

Thanks Koen for the fast answer! Problem isn't to get the stats, it is to display them... Multiple tables are by groups, my output need to be .rtf or .xlsx file ready for user in this format but 10x10 classes : 

	Rows_group
Lines_group	Frequency Percent Row percent Col percent Value mean Proportion mean

I was thinking to build 'manually' the table and display results with proc report or proc tabulate but it was getting tricky, I ended thinking it wasn't the good approach. Proc freq gives me most important stats user need, but I been asked if I can add means of variables which created the classes. and I like to please users who have good use of stats!!!

I'm still trying to find an idea but I'm stuck in circular reasoning and worse case it will end in 2 separate tables but it wouldn't be nice and finished job...

Thanks again!

Marc

[sbxkoenk · Accepted solution]

You definitely want to use PROC REPORT for this !

• Paper 3582-2019
  SuperFreq: Using the REPORT Procedure to Create Multi-Level Frequency Tables
  Jedediah J Teres, MDRC
  https://support.sas.com/resources/papers/proceedings19/3582-2019.pdf
• Getting Freqy with PROC REPORT
  Ethan Miller, SRI International, Menlo Park, Ca
  https://www.lexjansen.com/wuss/2011/tut/Papers_Miller_E_76116.pdf
  This paper will illustrate how to produce frequencies, cross tabulations, and means using PROC REPORT, and show various style options that can be used with ODS.
• Paper AA700 -- Where Proc FREQ ends and Proc REPORT begins
  Thushan Wijesinghe, LexisNexis, Miamisburg, OH
  https://www.lexjansen.com/mwsug/2005/Application_Development/AA700.pdf

Koen

[marc11235]

Thanks again Koen, it comforts me that intuition was good! I'm mostly using proc report to make lists, but I did know it was much more powerful... With that documentation, I have everything to customize my output as needed. I was probably trying too much to create a matrix style result and simply display it but indeed I can put "raw" data in proc report and tell it what to do with them!

Have a very good day!

Marc

[marc11235]

Hello Koen, just to share results :

I tried with proc report but I wasn't able to calculate statistics by rows e.g. row proportion... I don't mind about the look of the report, I need numbers to be all within a single page ready to use. Here is what I finally obtained with proc sql and proc tabulate, it can handle any 2 dimensions table size with any kind of stats included :

data status;
input row_group $ col_group $ value proportion tout $;
datalines;
0$-15$ 0%-40% 10 30 all
26$-35$ 41%-60% 34 41 all
16$-25$ 41%-60% 22 51 all
26$-35$ 41%-60% 26 54 all
16$-25$ 61%-100% 17 90 all
26$-35$ 0%-40% 32 10 all
26$-35$ 61%-100% 30 78 all
26$-35$ 41%-60% 26 45 all
16$-25$ 0%-40% 22 12 all
;
run;
/* total global */
proc sql;
create table status2 as
select *,
count(tout) as n_obs,
mean(value) as tot_val_mean,
mean(proportion) as tot_prop_mean
from status;
quit;
proc sort data=status2 out=tot nodupkey;
by tout;
run;
data tot;
set tot;
row_group="total";
col_group="total";
rename tot_val_mean=val_mean;
rename tot_prop_mean=prop_mean;
rename n_obs=freq;
pct=100;
col_pct=100;
row_pct=100;
drop value proportion tout;
run;

/* total colonnes */
proc sql;
create table status3 as
select *,
count(tout) as col_freq,
100 as col_pct,
count(tout)/n_obs as pct,
100 as row_pct,
mean(value) as col_val_mean,
mean(proportion) as col_prop_mean
from status2
group by col_group;
quit;
proc sort data=status3 out=col_tot nodupkey;
by col_group;
run;
data col_tot;
set col_tot;
row_group="total";
rename col_freq=freq;
rename col_val_mean=val_mean;
rename col_prop_mean=prop_mean;
drop value proportion tout N_obs tot_val_mean tot_prop_mean;
run;

/* total lignes */
proc sql;
create table status4 as
select *,
count(tout) as row_freq,
100 as row_pct,
count(tout)/n_obs as pct,
100 as col_pct,
mean(value) as row_val_mean,
mean(proportion) as row_prop_mean
from status3
group by row_group;
quit;
proc sort data=status4 out=row_tot nodupkey;
by row_group;
run;
data row_tot;
set row_tot;
col_group="total";
rename row_freq=freq;
rename row_val_mean=val_mean;
rename row_prop_mean=prop_mean;
drop value proportion tout N_obs tot_val_mean tot_prop_mean col_freq col_val_mean col_prop_mean;
run;

/* total cellules */
proc sql;
create table status5 as
select col_group,
row_group,
count(tout) as cell_freq,
count(tout)/N_obs as pct,
count(tout)/col_freq as col_pct,
count(tout)/row_freq as row_pct,
mean(value) as cell_val_mean,
mean(proportion) as cell_prop_mean
from status4
group by col_group, row_group;
quit;
proc sort data=status5 out=cell_tot nodupkey;
by col_group row_group;
run;
data cell_tot;
set cell_tot;
rename cell_freq=freq;
rename cell_val_mean=val_mean;
rename cell_prop_mean=prop_mean;
run;

data tout;
set cell_tot col_tot row_tot tot;
run;

proc tabulate data=tout;
class col_group row_group;
var freq pct col_pct row_pct val_mean prop_mean;
table row_group*(freq pct col_pct row_pct val_mean prop_mean),
col_group;
run;

code isn't cleaned but it works, i felt like recoding proc freq the way i would like it ahah!

Thanks again and sorry if my english isn't as good as if it would be natural to me!

Marc