turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Proc Freq Results as Data Table

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-25-2017 11:35 PM - edited 03-25-2017 11:36 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 10:18 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 01:06 AM

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

- output statement

- out= option on the table statement

- the ODS data destination

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 01:24 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 01:48 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 06:45 AM

Add one more proc transpose.

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 01:02 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 02:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

03-27-2017 07:53 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jl1005

03-26-2017 10:18 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

03-27-2017 07:54 PM

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