BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Miracle
Barite | Level 11

Hi there.

Can I please ask your help to get my desired data set because I am having diffuilt. Smiley Frustrated

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.Smiley Happy

 

FamIDFamMemIDHSurvIDHMemIDRelationshiptime
1111Householder1
1212Father1
1313Son1
1414Daughter1
2121Grandfather1
2222Son1
2323Stepspn1
2323Stepspn2
20000124Friend1
30000125Flatmate1
40000131Flatmate1
50000132Flatmate1
50000132Flatmate2
60000133Boarder1

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;


View solution in original post

6 REPLIES 6
Ksharp
Super User
Can you explain what is your logic ? 
FamID ,FamMemID is the columns you want to create ?
Shmuel
Garnet | Level 18

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;

Miracle
Barite | Level 11

Hi @Ksharp @Shmuel

I have just edited my post.

Hope it is much clearer now.

Thank you. Smiley Happy

Shmuel
Garnet | Level 18

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;

Reeza
Super User

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;

 

Ksharp
Super User
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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1133 views
  • 1 like
  • 4 in conversation