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;
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.