Merge two records into one

[Demographer]

Hi,

I have a dataset in which each line is a person with variables such as age, sex, language, etc. There is also a variable that links people living in a same household (HHNUM). With those data, I want to create a new dataset that keep all women aged between 15 and 49 years old, with a new variable that indicates if there is a child aged between 0 and 1 in her household. I guess it's a routine step, but I don't know what to do for this purpose.

Thanks,

PS. This board is really awesome. Thank for all. I hope I will be able to help other users when I'll be more familiar with SAS.

[AncaTilea · Accepted solution]

Hi.

Here is a very not most efficient code:

data have;

    input age sex $ hhnum;

    cards;

0 F 1

10 M 1

45 F 1

43 M 1

10 F 2

10 M 2

15 F 2

43 M 2

;

*create an indicator for child in household or not;

data temp(keep = hhnum child_flag);

    set have;

child_flag = ( age = 0 or age = 1);

run;

proc sort data = temp;by hhnum child_flag;run;

data temp2;

    set temp;

    by hhnum;

    if last.hhnum;

run;

data want;

    merge have(where = (sex = "F" & 15 <= age <= 49))

          temp2;

    by hhnum;

run;

let me know if it works?

[Demographer]

It seems a good way. I'll give you feedback later, maybe in one week.

[Haikuo]

If you don't mind tasting some flavor of Proc SQL, here is another way:

proc sql;

  create table want as

  select *, case when sum(0<=age<=1)>0 then 1 else 0 end as child_flag

from have

group by hhnum

having sex='F' and 15<=age<=49

;

quit;

Haikuo