topic Re: tabulate and freq in SAS Programming

tabulate and freq

radhikaa4 — Fri, 13 Dec 2019 20:05:46 GMT

I have the following table:

ID	reason_1	reason_2	reason_3
1	1	1	0
2	1	0	0
3	0	1	0
4	0	1	1
5
6	1	0	0
7	1	1	0
8	0	1	1
9	0	0	1
10	1	1	0

I would like to assign 1=yes, and 0= no and then calculate frequency and count by each reason by category

Something like this:

	no_count	no_pct	yes_count	yes_percent
reason_1	4	0.444444	5	55.6%
reason_2	3	0.333333	6	66.7%
reason_3	6	0.666667	3	33.3%

I tried the following:

proc freq data=test;

tables reason1 reason2 reason3;

run;

Re: tabulate and freq

Kurt_Bremser — Fri, 13 Dec 2019 20:10:54 GMT

Transpose to a long format, filter out observations where col1 = 0, and run proc freq on _name_.

Re: tabulate and freq

Kurt_Bremser — Fri, 13 Dec 2019 20:16:14 GMT

PS you should also start studying the many answers you got to similar questions in your other threads, as you might already have a proper answer there.

Re: tabulate and freq

jvdl — Fri, 13 Dec 2019 20:18:52 GMT

Try this and just transpose the stats output on your own.

data have;
	input id reason_1 reason_2 reason_3;
	cards;
1 1 1 0
2 1 0 0
3 0 1 0
4 0 1 1
5 . . .
6 1 0 0
7 1 1 0
8 0 1 1
9 0 0 1
10 1 1 0
run;

proc sort data=have;
	by id;
run;

proc transpose data=have out=t(rename=(_name_=reason col1=answer));
	by id;
run;

proc sort data=t;
	by reason;
run;

proc freq data=t noprint;
	by reason;
	tables reason * answer / out=stats(where=(not missing(answer)));
run;

Re: tabulate and freq

ed_sas_member — Fri, 13 Dec 2019 21:29:48 GMT

Hi @radhikaa4

You can try this, using a proc format to specify Yes / No instead of 1 / 0:

data have;
	input id reason_1 reason_2 reason_3;
	cards;
1 1 1 0
2 1 0 0
3 0 1 0
4 0 1 1
5 . . .
6 1 0 0
7 1 1 0
8 0 1 1
9 0 0 1
10 1 1 0
;
run;

proc format fmtlib;
	value reason 
		0 = "No"
		1 = "Yes";
run;

data want;
	set have;
	format value reason.;
	by id;
	array reason_(3);
	do i=1 to dim(reason_);
		reason = vname(reason_(i));
		value = reason_(i);
		output;
	end;
	drop i reason_:;
run;

Output using proc freq:

proc freq data=want;
	tables reason * value / nocum nocol nopercent;
run;

or output using proc report:


proc report data=want;
	column reason value, (n pct);
	define reason / group 'Reason';
	define value / across;
	define n / "n" ;
	define pct / computed "%" f=percent8.0;
	
	/* Sum totals in row for further calculation */
	compute before reason;
 	    _total = sum(_c2_, _c4_);
    endcomp;
    
    /* Compute percentages from frequencies (n) and totals in row (_total) */
    compute pct;
		if _c2_> 0 then _c3_ = _c2_ / _total;
		else _c3_= 0; 
		if _c4_> 0 then _c5_ = _c4_ / _total;
		else _c5_= 0; 
	endcomp;

;
run;

Re: tabulate and freq

data_null__ — Fri, 13 Dec 2019 21:59:12 GMT

Usually for indicator variables you are only interested in YES and it is easy to get that with PROC MEANS;

data have;
	input id reason_1-reason_3;
	cards;
1 1 1 0
2 1 0 0
3 0 1 0
4 0 1 1
5 . . .
6 1 0 0
7 1 1 0
8 0 1 1
9 0 0 1
10 1 1 0
;;;;
   run;
ods select none;
proc means data=have n sum mean stackods;
   var reason:;
   ods output summary=summary;
   run;
ods select all;
proc print data=summary;
   run;

Re: tabulate and freq

Ksharp — Sat, 14 Dec 2019 10:13:24 GMT

EDITED.

data have;
	input id reason_1-reason_3;
	cards;
1 1 1 0
2 1 0 0
3 0 1 0
4 0 1 1
5 . . .
6 1 0 0
7 1 1 0
8 0 1 1
9 0 0 1
10 1 1 0
;;;;
proc sql;
create table want as
select 'reason_1 ' as reason,
sum(reason_1=0) as no_count,calculated no_count/count(reason_1) as no_percent ,
sum(reason_1=1) as yes_count,calculated yes_count/count(reason_1) as yes_percent
from have
union
select 'reason_2 ' as reason,
sum(reason_2=0) as no_count,calculated no_count/count(reason_2) as no_percent,
sum(reason_2=1) as yes_count,calculated yes_count/count(reason_2) as yes_percent
from have
union
select 'reason_3 ' as reason,
sum(reason_3=0) as no_count,calculated no_count/count(reason_3) as no_percent,
sum(reason_3=1) as yes_count,calculated yes_count/count(reason_3) as yes_percent
from have;
quit;