Proc Freq Question

[SASMom2]

I have a table XYZ and I am running below code to get frequency of two fields in that table.

           proc freq data =  XYZ;
           tables Pre_intent*T4 / nopercent norow nocol;    run;

I get below output.

Table of Pre_intent by T4
Pre_intent	T4
	1 ER	2 UC	3 OV	Total
1 ER	9	1	176	376
2 UC	26	12	1450	4333
3 OV	7	2	539	1576
Total	42	15	2165	6285

I would like to add a 'percentage of total' field so it would look like below.

Table of Pre_intent by T4
Pre_intent	T4
	1 ER	2 UC	3 OV	Total	Percentage of Total
1 ER	9	1	176	376	0.05982498
2 UC	26	12	1450	4333	0.689419252
3 OV	7	2	539	1576	0.250755768
Total	42	15	2165	6285	1

Also, how do I save this frequency table as a dataset???

Thanks in advance for all your help!

[Tom]

PROC FREQ can show the row (or column) percentages, but you have asked it not to.

You need to explain what your data is and what analysis you are doing?

It looks to me like your example data is missing one category of the COLUMN variable.  Instead of having all four categories you have counts for three of the four and a separate TOTAL value.  I can get PROC FREQ to calculate the 5.98% you show for the first row if I first just calculate the count for the missing category.

data have;
  row+1;
  do col=1 to 4;
    input count @ ;
    if col=4 then count=count-cum;
    output;
    cum=sum(cum,count);
  end;
  drop cum;
cards;
9 1 176 376
26 12 1450 4333
7 2 539 1576
;

proc freq data=have ;
  tables row*col ;
  weight count;
run;

[SASMom2]

Thanks Tom for your reply.

I need to see the 'Percentage of Totals' in a separate column as shown in the original post.

[ballardw · Accepted solution]

Perhaps a different reporting procedure. Using the data set provided by @Tom :

proc tabulate data=have;
   class row col;
   freq count;
   table row='' all='Total',
         col*n='' all=''*( n='Total' pctn='%')
         /box=row
   ;
run; 

Which gives something like:

row	col
	1	2	3	4	Total	%
1	9	1	176	190	376	5.98
2	26	12	1450	2845	4333	68.94
3	7	2	539	1028	1576	25.08
Total	42	15	2165	4063	6285	100.00

[SASMom2]

Thanks Ballardw That worked!!!

One more question...How do I save this table?

[ballardw]

---

@SASMom2 wrote:

Thanks Ballardw That worked!!!

One more question...How do I save this table?

---

It depends on what you mean by "save this table".

You use ODS to send the result to a file such as RTF , PDF or html.

If you mean a data set then that's a different story. Proc Tabulate will create data sets BUT they don't "look" anything like the displayed output because of the need for including statistics and indicators for such things as which table (tabulate supports multiple table statements) and which columns/rows are related to summary lines (the All results). And and OUT=yourdatasetname to the Proc statement to see what the data set would look  like.

[Ksharp]

data have;
 set sashelp.heart;
 keep bp_status smoking_status;
 run;

proc sql;
create table temp as
select 1 as id1,bp_status,1 as id2,smoking_status,count(*) as n
 from have 
  where bp_status is not missing and smoking_status is not missing
   group by bp_status,smoking_status
union all
select 1,bp_status,2,'Total',count(*)
 from have 
  where bp_status is not missing and smoking_status is not missing
   group by bp_status
union all
select 1,bp_status,3,'Pct of Total',count(*)/(select count(*) from have where bp_status is not missing and smoking_status is not missing)
 from have 
  where bp_status is not missing and smoking_status is not missing
   group by bp_status
union all
select 2,'Total',1,smoking_status,count(*)
 from have 
  where bp_status is not missing and smoking_status is not missing
   group by smoking_status
union all
select 2,'Total',2,'Total',count(*)
 from have 
  where bp_status is not missing and smoking_status is not missing
union all
select 2,'Total',3,'Pct of Total',1
 from have(obs=1)
order by 1,2,3,4
;
quit;
proc transpose data=temp out=want(drop=id1 _name_);
by id1 bp_status;
var n;
id smoking_status;
run;

[SASMom2]

Thank you all for your help. I used the proc tabulate and works beautifully!