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.
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.
You can use a KEEP on the output dataset to keep only the variables you care about.
Tables.../out=want(keep = ...);
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;
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?
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
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?
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.
The first code you posted worked perfectly, thank you. It's much more efficient than what I had come up with.
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 25. Read more here about why you should contribute and what is in it for you!
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.