topic Re: Replace id with first occurrence in SAS Enterprise Guide

Replace id with first occurrence

Sean_OConnor — Mon, 12 Jun 2017 11:31:35 GMT

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;

Re: Replace id with first occurrence

Kurt_Bremser — Mon, 12 Jun 2017 11:49:00 GMT

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;

Re: Replace id with first occurrence

RW9 — Mon, 12 Jun 2017 11:51:02 GMT

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;