Solved
Contributor
Posts: 47

# Proc Freq Results as Data Table

[ Edited ]

Hi,

I'm using proc freq to create a transition matrix. I'm getting the table I want in the results tab, but would like to have it output as a seperate data table as well. Here's the code I'm currently using to get the table:

``````proc freq data = events_limited;
tables start_bases_cd*end_bases_cd /outpct nopercent nocol nofreq;
Title 'Transitions';
run;``````

and here's the results that I would like to be stored in a table:

What's the best way to acheive this?

Thank you.

Accepted Solutions
Solution
‎03-27-2017 07:54 PM
Posts: 1,337

## Re: Proc Freq Results as Data Table

I think you want a file with one row per START_BASES_CD and one var per value of END_BASES_CD.

``````proc freq data = events_limited;
tables start_bases_cd*end_bases_cd / noprint sparse out=need1a;
tables start_bases_cd /noprint out=need1b;
run;

data want;
merge need1a  need1b (rename=(count=row_total));
by start_bases_cd;
array  pct {0:7} percent_0-percent_7;
retain percent_:;
if first.start_bases_cd then call missing(of pct{*});
pct{end_bases_cd}=100*count/row_total;
drop percent count row_total end_bases_cd;
if last.start_bases_cd;
run;
``````

t uses proc freq to make  two output data  sets, one with counts of each start/end combination, and one with total counts for each start value. Then the second step merges them and generates the percent vars.  Note this program assumes you really do have codes=0,1,2,...7.

If you don't have exact codes 0 through 7, this next program will handle the general case:

``````proc freq data = events_limited;
tables start_bases_cd*end_bases_cd / noprint sparse out=need1a;
tables start_bases_cd /noprint out=need1b;
run;

data  need2;
merge need1a  need1b (rename=(count=row_total));
by start_bases_cd;
percent=100*count/row_total;
drop count row_total;
run;

proc transpose data=need2 out=want2 (drop=_name_ _label_) prefix=percent_;
by start_bases_cd;
var percent;
id end_bases_cd;
run;
``````

This one starts with the same PROC FREQ.  It merges the two data sets and lets proc transpose name the resulting variables.

All Replies
Super User
Posts: 5,876

## Re: Proc Freq Results as Data Table

Without digging into your exact requirement, there are (at least) three possibilities to get output data:
- output statement
- out= option on the table statement
- the ODS data destination
Data never sleeps
Contributor
Posts: 47

## Re: Proc Freq Results as Data Table

I've tried the out= option, but couldn't find a way to just return row percent. That's the only statistic I'd like in the final output.
Super User
Posts: 23,700

## Re: Proc Freq Results as Data Table

You can use a KEEP on the output dataset to keep only the variables you care about.

Tables.../out=want(keep = ...);

Super User
Posts: 10,770

## Re: Proc Freq Results as Data Table

``````

proc freq data = sashelp.class noprint;
tables sex*age / out=x outpct nopercent nocol nofreq ;
Title 'Transitions';
run;

proc transpose data=x out=want;
by sex;
id age;
var pct_row;
run;

proc print;run;``````
Contributor
Posts: 47

## Re: Proc Freq Results as Data Table

Okay, so taking everyones advice, here what the code ended up looking like:

``````proc freq data = events_limited;
tables start_bases_cd*end_bases_cd / out=transition outpct nopercent nocol nofreq;
Title 'Transitions';
run;
proc transpose data = transition out=transition_1;
by start_bases_cd;
id end_bases_cd;
var pct_row;
run;
proc stdize data =  transition_1 out = transition_2 reponly missing=0;
proc sql;
create table transition_3 AS
select start_bases_cd, '   0'n/100 as end_0 format D6.4, '   1'n/100 as end_1 format D6.4, '   2'n/100 as end_2 format D6.4, '   3'n/100 as end_3 format D6.4
, '   4'n/100 as end_4 format D6.4, '   5'n/100 as end_5 format D6.4, '   6'n/100 as end_6 format D6.4, '   7'n/100 as end_7 format D6.4
from transition_2
;
quit;``````

The stdize was to insert 0's where the field was blank and the sql was to force the columns back in order.

In regards to formatting and renaming the columns, is there a more efficient way to accomplish this?

Also, in one field only theres a number written as scientific notation. Is there an easy way to force that to round to 0?

SAS Super FREQ
Posts: 9,367

## Re: Proc Freq Results as Data Table

Hi:

I am still not sure what you mean by the original request that your report from PROC FREQ should be "stored in a table" -- do you really mean you want a SAS data set that looks "exactly" like the report you provided? Or, do you just want the report to be in a different format, such as in an Excel worksheet or in a Word doc?

You did not really define what you want other than to say "stored in a table" -- but did not provide more information about the ultimate usage of a table that stores a report. While there are many convoluted ways to take your report and get it into a dataset, there are many easier ways to make a Word doc or an Excel sheet out of this data. Can you elaborate on what the final usage of this table will be?

cynthia

Contributor
Posts: 47

## Re: Proc Freq Results as Data Table

Hi,

Essentially I wanted to results I had posted above in a picture to be stored exactly as is, or at least very close to that in a SAS data set. Does that clear it up?

Solution
‎03-27-2017 07:54 PM
Posts: 1,337

## Re: Proc Freq Results as Data Table

I think you want a file with one row per START_BASES_CD and one var per value of END_BASES_CD.

``````proc freq data = events_limited;
tables start_bases_cd*end_bases_cd / noprint sparse out=need1a;
tables start_bases_cd /noprint out=need1b;
run;

data want;
merge need1a  need1b (rename=(count=row_total));
by start_bases_cd;
array  pct {0:7} percent_0-percent_7;
retain percent_:;
if first.start_bases_cd then call missing(of pct{*});
pct{end_bases_cd}=100*count/row_total;
drop percent count row_total end_bases_cd;
if last.start_bases_cd;
run;
``````

t uses proc freq to make  two output data  sets, one with counts of each start/end combination, and one with total counts for each start value. Then the second step merges them and generates the percent vars.  Note this program assumes you really do have codes=0,1,2,...7.

If you don't have exact codes 0 through 7, this next program will handle the general case:

``````proc freq data = events_limited;
tables start_bases_cd*end_bases_cd / noprint sparse out=need1a;
tables start_bases_cd /noprint out=need1b;
run;

data  need2;
merge need1a  need1b (rename=(count=row_total));
by start_bases_cd;
percent=100*count/row_total;
drop count row_total;
run;

proc transpose data=need2 out=want2 (drop=_name_ _label_) prefix=percent_;
by start_bases_cd;
var percent;
id end_bases_cd;
run;
``````

This one starts with the same PROC FREQ.  It merges the two data sets and lets proc transpose name the resulting variables.

Contributor
Posts: 47

## Re: Proc Freq Results as Data Table

The first code you posted worked perfectly, thank you. It's much more efficient than what I had come up with.

☑ This topic is solved.