Create conditional ID

[Miracle]

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

[Ksharp]

Can you explain what is your logic ? 
FamID ,FamMemID is the columns you want to create ?

[Shmuel]

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]

Hi @Ksharp @Shmuel, 

I have just edited my post.

Hope it is much clearer now.

Thank you. 

[Shmuel]

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]

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 · Accepted solution]

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;