topic Re: How do I find the most commonly occurring sex, across different events, for unique personid? in SAS Programming

How do I find the most commonly occurring sex, across different events, for unique personid?

Annabeth — Thu, 06 Jun 2019 06:35:37 GMT

Hi there,

I have data from 2005 to 2016. The counting unit of the data is event_id, each row is one event id and has demographic data associated with that event. One of the variables in the dataset is a unique person identifier we shall call it personid, a person may have many different events. My boss would like me to count unique people by sex. However, as the data is of interesting quality you might have say 7 different events for one personid and they might say FEMALE 5 times then MALE once and unknown once. I would like to, over the entire collection (forgetting about year), attach the most commonly occurring sex to the unique person identifier, then I guess join the associated event ids to the person identifier for each year. I will make another data set for unique people at this point that I can pull from.

Since there are like over 400,000 records/events, how do I go about finding the most occurring value for sex and attach it to the person identifier? (the more efficient the better). Using SAS 9.4

Thank you. An example of what my data looks like below:

event id PERSONID sex other demographic year

86590 767898 FEMALE xxx 2005

86591 767898 FEMALE xxx 2005

86592 767898 MALE xxx 2007

86593 767898 UNKNOWN xxx 2008

Thanks in advance for your help!

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Jagadishkatam — Thu, 06 Jun 2019 08:00:12 GMT

please try the proc sql

proc sql;
create table want as select count(personid) as count, eventid,personid,sex,year from have 
group by eventid,personid,sex,year
order by eventid,personid,sex,year;
quit;

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Kurt_Bremser — Thu, 06 Jun 2019 08:51:16 GMT

You need to create an intermediary table for use in a subquery (I tried to pack it into a single subquery, but that would either fail or become quite unwieldy)

data have;
input event_id $ personid $ sex $ other_dem $ year;
datalines;
86590 767898 FEMALE  xxx 2005
86591 767898 FEMALE  xxx 2005
86592 767898 MALE    xxx 2007
86593 767898 UNKNOWN xxx 2008
;

proc sql;
create table sex as
select personid, sex, count(*) as count from have
group by personid, sex
;
create table want as
select
  a.event_id,
  a.personid,
  (
    select b.sex
    from sex b
    where b.personid = a.personid
    group by personid
    having count = max(count)
  ) as sex,
  a.other_dem,
  a.year
from have a
;
quit;

Note how I presented your example data in readily usable form by putting it into a data step with datalines. Please do so yourself in the future, as it makes helping you a lot easier for us.

You can also do it in a data step by using double do loops:

proc sort data=have;
by personid sex;
run;

data want;
maxcount = 0;
do until (last.personid);
  count = 0;
  do until (last.sex);
    set have;
    by personid sex;
    count + 1;
  end;
  if count > maxcount
  then do;
    maxcount = count;
    newsex = sex;
  end;
end;
do until (last.personid);
  set have;
  by personid;
  sex = newsex;
  output;
end;
drop count maxcount newsex;
run;

proc sort data=want;
by event_id;
run;

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Patrick — Thu, 06 Jun 2019 09:24:54 GMT

What should happen in case of ties?

@Kurt_Bremser I believe your SQL falls over if there are ties.

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Kurt_Bremser — Thu, 06 Jun 2019 10:08:37 GMT

Yep. I'm much more confident with the data step for this, as the preceding sort may be used to set a preference in case of ties.

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Annabeth — Tue, 11 Jun 2019 00:49:36 GMT

Hi there,

Thank you for all of your help! I have been on holiday so only just been able to use and the do loop is working well!
When you say that you could set a preference in the preceding sort in the case of ties - how would I do that? Sorry I am such a beginner! For instance, when there is FEMALE and UNKNOWN or MALE and UNKNOWN that are ties I would like it to not be unknown and if there is FEMALE or MALE, I would like it to be deleted!

How would you do that?

Thank you in advance.

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Kurt_Bremser — Tue, 11 Jun 2019 05:29:41 GMT

With your original data, UNKNOWN filters itself out if it's part of a tie. Keep in mind that it is the last in the alphabetic sorting sequence (F-M-U), and if its count is not larger than any other count, F or M will take precedence.

For a tie of F and M, you can check that in and after the first do loop in the data step. Keep separate counters for F and M, and if they are equal after the do loop, set to unknown or missing.

Re: How do I find the most commonly occurring sex, across different events, for unique personid?

Annabeth — Tue, 11 Jun 2019 06:50:16 GMT

Amazing. Thank you!