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;
Transpose to a long format, filter out observations where col1 = 0, and run proc freq on _name_.
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.
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;
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;
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;
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;
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.