topic Re: Create conditional ID in SAS Programming

Create conditional ID

Miracle — Wed, 21 Sep 2016 05:44:38 GMT

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

Re: Create conditional ID

Ksharp — Wed, 21 Sep 2016 04:51:44 GMT

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

Re: Create conditional ID

Shmuel — Wed, 21 Sep 2016 05:05:51 GMT

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;

Re: Create conditional ID

Miracle — Wed, 21 Sep 2016 05:47:55 GMT

Hi @Ksharp @Shmuel,

I have just edited my post.

Hope it is much clearer now.

Thank you.

Re: Create conditional ID

Reeza — Wed, 21 Sep 2016 05:51:25 GMT

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;

Re: Create conditional ID

Shmuel — Wed, 21 Sep 2016 05:56:44 GMT

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;

Re: Create conditional ID

Ksharp — Wed, 21 Sep 2016 06:40:01 GMT

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;