topic Re: Proc Freq Question in SAS Programming

Proc Freq Question

SASMom2 — Tue, 19 Nov 2024 18:56:55 GMT

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!

Re: Proc Freq Question

Tom — Tue, 19 Nov 2024 19:41:11 GMT

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;

Re: Proc Freq Question

ballardw — Tue, 19 Nov 2024 20:58:19 GMT

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
row	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

Re: Proc Freq Question

SASMom2 — Tue, 19 Nov 2024 20:07:48 GMT

Thanks Tom for your reply.

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

Re: Proc Freq Question

SASMom2 — Wed, 20 Nov 2024 00:01:51 GMT

Thanks Ballardw That worked!!!

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

Re: Proc Freq Question

Ksharp — Wed, 20 Nov 2024 01:27:44 GMT

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;

Re: Proc Freq Question

ballardw — Wed, 20 Nov 2024 08:04:46 GMT

@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.

Re: Proc Freq Question

SASMom2 — Wed, 20 Nov 2024 20:42:31 GMT

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