Replace id with first occurrence

[Sean_OConnor]

Folks,

I have a dataset of individuals which I would like to assign to households. I've noticed in my data that in some cases certain individuals have been assigned to to two different houses.

Thus, I would like to tell SAS to overwrite this second household id with the first instance. However, when I run my code, these second values are replaced with a 1. Any advice would be most welcome.

Here is my data at the start;

Person_Id	Relation_Id	House_Id
01000N	035608N	42D00
01000N	1011E	4D2106

Here is what I want;

Person_Id	Relation_Id	House_Id
01000N	035608N	42D00
01000N	1011E	42D00

This is what happens when I run my code;

Person_Id	Relation_Id	House_Id
01000N	035608N	42D00
01000N	1011E	1

proc sort data=households_2013 out=test; by Person_id house_id;run;
data trial;
set test;
by person_id house_id;
if not first.person_id and last.person_id then house_id= first.house_id;run;

[Kurt_Bremser · Accepted solution]

Th first. and last. variables automatically created by the use of a by statement are boolean variables and can only take the values 0 (false) or 1 (true).

You will need to keep the house_id in a retained variable:

proc sort
  data=households_2013
  out=test
;
by person_id house_id;
run;

data trial;
set test;
by person_id;
retain save_house;
if first.person_id
then save_house = house_id;
else house_id = save_house;
drop save_house;
run;

[RW9]

Hi,

You are nearly there:

proc sort data=households_2013 out=test;
  by person_id house_id;
run;

data trial;
  set test;
  by person_id house_id;
  retain new_house_id;
  if first.person_id then new_house_id=house_id;
run;