SAS create ID based upon other variables

Question

I have the following dataset detailing the ages of women present in a household :

Household ID     Age
     1            19
     2            52
     2            22
     2            18
     3            37
     3            29

I would like to add a third column to this table which gives an ID to each women in the household from 1 to n, where n is the number of women in the household. So this would give the following :

Household ID     Age     Woman ID
     1            19        1
     2            52        1
     2            22        2
     2            18        3
     3            37        1
     3            29        2

How can I achieve this ?

Keneni Keneni · Accepted Answer · 2014-04-18T13:16:32

First make sure that the Household ID is sorted. Then using First. should give you what you need.

proc sort data = old;
  by Household_ID;
run;

data new(rename= (count=woman_id));
  set old;
  count + 1;
  by Household_ID;
  if first.Household_ID then count = 1;
run;

SAS create ID based upon other variables

1 Answers