Desktop productivity for business analysts and programmers

Replace id with first occurrence

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Replace id with first occurrence

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_IdRelation_IdHouse_Id
01000N035608N42D00
01000N1011E4D2106

 

Here is what I want;

 

 

Person_IdRelation_IdHouse_Id
01000N035608N42D00
01000N1011E42D00

 

 

This is what happens when I run my code;

 

Person_IdRelation_IdHouse_Id
01000N035608N42D00
01000N1011E1

 

 

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;

Accepted Solutions
Solution
‎06-12-2017 08:57 AM
Super User
Posts: 7,371

Re: Replace id with first occurrence

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎06-12-2017 08:57 AM
Super User
Posts: 7,371

Re: Replace id with first occurrence

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,668

Re: Replace id with first occurrence

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 117 views
  • 0 likes
  • 3 in conversation