topic Proc Freq Results as Data Table in SAS Programming

Proc Freq Results as Data Table

jl1005 — Sun, 26 Mar 2017 03:36:00 GMT

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.

Re: Proc Freq Results as Data Table

LinusH — Sun, 26 Mar 2017 05:06:39 GMT

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

Re: Proc Freq Results as Data Table

jl1005 — Sun, 26 Mar 2017 05:24:51 GMT

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.

Re: Proc Freq Results as Data Table

Reeza — Sun, 26 Mar 2017 05:48:22 GMT

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

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

Re: Proc Freq Results as Data Table

Ksharp — Sun, 26 Mar 2017 10:45:13 GMT

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;

Re: Proc Freq Results as Data Table

jl1005 — Sun, 26 Mar 2017 17:02:05 GMT

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?

Re: Proc Freq Results as Data Table

Cynthia_sas — Sun, 26 Mar 2017 18:12:59 GMT

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

Re: Proc Freq Results as Data Table

mkeintz — Mon, 27 Mar 2017 02:18:54 GMT

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.

Re: Proc Freq Results as Data Table

jl1005 — Mon, 27 Mar 2017 23:53:24 GMT

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?

Re: Proc Freq Results as Data Table

jl1005 — Mon, 27 Mar 2017 23:54:14 GMT

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