Proc Freq and unique id values

Question

I have a table with two columns, ID and Gender as below

I am trying to count the number of males and females. I wrote a code like this

Proc Freq data=Work.Test1; tables gender; run;

The output i got was 5males and 2 females, I know this is wrong because Id repeats many times , there are only 2 males and 1 female. My question is how do i change Proc Freq so that I get the count for gender (males and Females) for unique Id values ?

Answer 1

You can use Nlevels in proc freq

Proc freq data= yourdata NLEVELS;
tables gender /noprint;
run;

Answer 2

I'm not sure if this is easy to do without using SQL or data step to work it out.

proc sql;
create table want as 
  select gender, count(distinct id) as count
  from have
  group by gender;
quit;

or (sorted by gender id)

data want;
set have;
by gender id;
if first.gender then count=0;
if first.id then count+1;
if last.gender then output;
run;

PROC TABULATE might be able to do what you want, but I couldn't figure a quick method out.

Answer 3

Try this:

proc sort data=have out=want nodupkey;
by id gender;

proc freq data=want;
tables gender;
run;

This will give you one record per ID/gender, then you can run your freq for gender.