Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: add statistics to proc freq output

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-25-2023 05:52 PM
(693 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Are you ready for the spotlight? We're accepting content ideas for **SAS Innovate 2025** to be held May 6-9 in Orlando, FL. The call is **open **until September 16. Read more here about **why** you should contribute and **what is in it** for you!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.