How to count distribution in numbers and percent of binary column in table?

Question

i have table in SAS Enterprise Guide like below:

Below table DOES NOT have duplicates in ID column.

ID  | TARGET 
----|--------
123 | 0
456 | 0 
777 | 1 
889 | 0 
122 | 1

And I would like to aggregate above table with sample data (original table has many more data) to have something like below:

Q_0 - number of ID with '0' in column TARGET
Q_1 - number of ID with '1' in column TARGET
P_0 - prcent of ID with '0' in column TARGET
P_1 - prcent of ID with '1' in column TARGET

Q_0 Q_1 P_0 P_1 COL1

3 2 0.6 0.4 XXX

How can I do that in SAS Enterprise Guide in normal SAS or in PROC SQL ?

Can you post a small representable sample of the sample data? — PeterClemmensen, Oct 23 '22 at 19:48
Peter, I edited my post, to better present problem and I add new column also :) is ok now for you ? — unbik, Oct 23 '22 at 19:56

score 1 · Accepted Answer · answered Oct 24 '22 at 01:58

PROC FREQ gives you the data but not in the desired format.

proc freq data=have;
table target /out=want outpct;
run;

If you really want that format, SQL is probably the easiest though the most manual. If you have missing values this probably needs to be adjusted.

proc sql;
create table want as
select sum(target=1) as q_1,
       sum(taget=0) as q_0, 
       mean(target) as p_1 format=percent12.1,
       1-mean(target) as p_0 format=percent12.1
from have;
quit;

How to count distribution in numbers and percent of binary column in table?

1 Answers1