Hi there.
Can I please ask your help to get my desired data set because I am having diffuilt.
I would like to create FamID and FamMemID based on the original dataset with HSurvID, HMemID, Relationship and time.
To differentiate between household and family, I would like to create the FamID with FamMemID.
TIme is the first,..., nth interview.
HSurvID is the household ID.
HMemID is the ID for household interviewee.
When Relationship is either Friend, Flatmate or Boarder, they will be thought as one new family on its own.
Thank you.
FamID | FamMemID | HSurvID | HMemID | Relationship | time |
1 | 1 | 1 | 1 | Householder | 1 |
1 | 2 | 1 | 2 | Father | 1 |
1 | 3 | 1 | 3 | Son | 1 |
1 | 4 | 1 | 4 | Daughter | 1 |
2 | 1 | 2 | 1 | Grandfather | 1 |
2 | 2 | 2 | 2 | Son | 1 |
2 | 3 | 2 | 3 | Stepspn | 1 |
2 | 3 | 2 | 3 | Stepspn | 2 |
20000 | 1 | 2 | 4 | Friend | 1 |
30000 | 1 | 2 | 5 | Flatmate | 1 |
40000 | 1 | 3 | 1 | Flatmate | 1 |
50000 | 1 | 3 | 2 | Flatmate | 1 |
50000 | 1 | 3 | 2 | Flatmate | 2 |
60000 | 1 | 3 | 3 | Boarder | 1 |
Still doesn't ring the bell. data have; informat famid fammemID hsurvid hmemid $8. relationship $12.; input FamID FamMemID $ HSurvID $ HMemID $ Relationship $ time $; cards; 1 1 1 1 Householder 1 1 2 1 2 Father 1 1 3 1 3 Son 1 1 4 1 4 Daughter 1 2 1 2 1 Grandfather 1 2 2 2 2 Son 1 2 3 2 3 Stepspn 1 2 3 2 3 Stepspn 2 20000 1 2 4 Friend 1 30000 1 2 5 Flatmate 1 40000 1 3 1 Flatmate 1 50000 1 3 2 Flatmate 1 50000 1 3 2 Flatmate 2 60000 1 3 3 Boarder 1 ; run; data want; set have; retain famID2 FamMemID2; by HSurvID HMemID notsorted; if first.HMemID then do; if relationship in ("Friend" "Flatmate" "Boarder") then do; famID2=int(ranuni(0)*1000000);FamMemID2=1; end; else do; famID2=FamID;FamMemID2=FamMemID; end; end;
Can you explain what is your logic ? FamID ,FamMemID is the columns you want to create ?
Skeleton program may be as:
data want;
set have (rename=(famid=old_famid fammemid = old_fammemid);
select (upcase(relatioship));
when ('HOUSEHOLDER') do;
famid = ... ; /* define new value to famid */
fammemid = ... ; /* define new value to fammemid */
end;
when ('FATHER') do;
famid = ... ; /* define new value to famid */
fammemid = ... ; /* define new value to fammemid */
end;
.... /* deal with next values */
end; /* end of select statement */
run;
Without entering the logic of what conditions lead to each change,
here is a new skeleton for your program:
data want;
set have;
if <conditions-1> then do; ... <define new values > ; end; else
if <conditions-2> then do; ... <define new values > ; end; else
... /* last variation: */
if <conditions-n> then do; ... <define new values > ; end;
run;
Not a full answer but hopefully illustrates the idea. Perhaps someone else can complete.
Your key variables appear to be hsurvid hmemid and time.
So using those in combination with BY and checking the values in Relationship are required. You'll need some extra variables to keep your IDs straight.
Hope this helps.
data have;
informat famid fammemID hsurvid hmemid $8. relationship $12.;
input FamID FamMemID $ HSurvID $ HMemID $ Relationship $ time $;
cards;
1 1 1 1 Householder 1
1 2 1 2 Father 1
1 3 1 3 Son 1
1 4 1 4 Daughter 1
2 1 2 1 Grandfather 1
2 2 2 2 Son 1
2 3 2 3 Stepspn 1
2 3 2 3 Stepspn 2
20000 1 2 4 Friend 1
30000 1 2 5 Flatmate 1
40000 1 3 1 Flatmate 1
50000 1 3 2 Flatmate 1
50000 1 3 2 Flatmate 2
60000 1 3 3 Boarder 1
;
run;
data want;
set have;
by hsurvid hmemid time;
retain famID1 0 memID 0 nonFam 0;
if first.hsurvid then do;
famID1+1;
nonFam=0;
memID=0;
end;
if relationship not in ("Friend" "Flatmate" "Boarder") then memID+1;
if relationship in ("Friend" "Flatmate" "Boarder") then do;
famID2=famID1*10000 + nonFam;
nonFam+1;
end;
run;
Still doesn't ring the bell. data have; informat famid fammemID hsurvid hmemid $8. relationship $12.; input FamID FamMemID $ HSurvID $ HMemID $ Relationship $ time $; cards; 1 1 1 1 Householder 1 1 2 1 2 Father 1 1 3 1 3 Son 1 1 4 1 4 Daughter 1 2 1 2 1 Grandfather 1 2 2 2 2 Son 1 2 3 2 3 Stepspn 1 2 3 2 3 Stepspn 2 20000 1 2 4 Friend 1 30000 1 2 5 Flatmate 1 40000 1 3 1 Flatmate 1 50000 1 3 2 Flatmate 1 50000 1 3 2 Flatmate 2 60000 1 3 3 Boarder 1 ; run; data want; set have; retain famID2 FamMemID2; by HSurvID HMemID notsorted; if first.HMemID then do; if relationship in ("Friend" "Flatmate" "Boarder") then do; famID2=int(ranuni(0)*1000000);FamMemID2=1; end; else do; famID2=FamID;FamMemID2=FamMemID; end; end;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: