BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jl1005
Obsidian | Level 7

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:

 

sas_output.JPG

 

 

What's the best way to acheive this?

 

 

Thank you.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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 hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20
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
jl1005
Obsidian | Level 7
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.
Reeza
Super User

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

 

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

Ksharp
Super User

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;
jl1005
Obsidian | Level 7

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?

Cynthia_sas
SAS Super FREQ

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

jl1005
Obsidian | Level 7

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?

mkeintz
PROC Star

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 hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
jl1005
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5546 views
  • 1 like
  • 6 in conversation