topic Re: Flag if two or more different clients share the same household ID in New SAS User

Flag if two or more different clients share the same household ID

HarryB — Fri, 10 May 2019 14:58:30 GMT

Hi,

I would truly appreciate anyone's help on the following issue.

I would like to flag if two or more different clients share the same household ID. In other words, I would like to know who is with their family.

My data structure:

ClientID HouseholdID

9804 4182

10230 4461

190928 120137

193909 120137

114631 77320

186869 77320

114631 77320

186869 77320

114631 77320

11635 5390

Want:

ClientID HouseholdID Flag

9804 4182 0

10230 4461 0

190928 120137 1

193909 120137 1

114631 77320 1

186869 77320 1

114631 77320 1

186869 77320 1

114631 77320 1

11635 5390 0

Thank you in advance!

Re: Flag if two or more different clients share the same household ID

ballardw — Fri, 10 May 2019 15:17:22 GMT

Are you sure you only want a single value? The flag as shown doesn't differentiate between the 120137 and 77320 houses. How do you actually expect to use that flag value?

Re: Flag if two or more different clients share the same household ID

HarryB — Fri, 10 May 2019 15:30:00 GMT

The flag value would be a binary variable that would say whether the client has a household (family). If one household ID is not shared by at least two clients, that means the client is not with the family. For example, the Household ID 4461 is only associated with one client 10230, which means this client is by him/herself.
Hope, this is clear. Thanks

Re: Flag if two or more different clients share the same household ID

Astounding — Fri, 10 May 2019 15:52:07 GMT

One approach:

data want;
   flag = 0;
   do until (last.HouseholdID);
      set have;
      by HouseholdID notsorted;
      prior_client = lag(clientID);
      if first.HouseholdID = 0 and prior_client ne clientID then flag=1;
   end;
   do until (last.HouseholdID);
      set have;
      by HouseholdID notsorted;
      output;
   end;
   drop prior_client;
run;

It looks right, but hasn't been tested. You have the data, so that part is up to you.

Re: Flag if two or more different clients share the same household ID

FreelanceReinh — Fri, 10 May 2019 18:18:36 GMT

Hi @HarryB,

Here's another approach:

proc sql;
create table want as
select *, (count(distinct ClientID)>1) as Flag
from have
group by HouseholdID;
quit;

Or, if you want to preserve the original sort order (rather than sort by HouseholdID), but you don't have a suitable sort key:

data _temp / view=_temp;
set have;
seqno=_n_;
run;

proc sql;
create table want(drop=seqno) as
select *, count(distinct ClientID)>1 as Flag
from _temp
group by HouseholdID
order by seqno;
quit;

Re: Flag if two or more different clients share the same household ID

HarryB — Fri, 17 May 2019 17:27:39 GMT

Thank you @FreelanceReinh and @Astounding

I made a minor edits on @Astounding 's code and made it work.

proc sort data=have;

by Household_ID;

run;

data want;

Group = 0;

do until (last.Household_ID);

set have;

by Household_ID notsorted;

prior_client = lag(client_uid);

if not missing(household_Id) and first.Household_ID = 0 and prior_client ne client_uid then Group=1;

end;

do until (last.Household_ID);

set have;

by Household_ID notsorted;

output;

end;

drop prior_client;

run;