DATA Step, Macro, Functions and more

Create conditional ID

Accepted Solution Solved
Reply
Regular Contributor
Posts: 228
Accepted Solution

Create conditional ID

[ Edited ]

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

 


Accepted Solutions
Solution
‎09-23-2016 02:21 AM
Super User
Posts: 10,018

Re: Create conditional ID

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


All Replies
Super User
Posts: 10,018

Re: Create conditional ID

Can you explain what is your logic ? 
FamID ,FamMemID is the columns you want to create ?
Trusted Advisor
Posts: 1,553

Re: Create conditional ID

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;

Regular Contributor
Posts: 228

Re: Create conditional ID

Hi @Ksharp @Shmuel

I have just edited my post.

Hope it is much clearer now.

Thank you. Smiley Happy

Trusted Advisor
Posts: 1,553

Re: Create conditional ID

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;

Super User
Posts: 19,768

Re: Create conditional ID

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;

 

Solution
‎09-23-2016 02:21 AM
Super User
Posts: 10,018

Re: Create conditional ID

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;


☑ This topic is solved.

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

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