topic Re: Fill age by condition in SAS Procedures

Fill age by condition

deleted_user — Wed, 23 Feb 2011 15:02:24 GMT

Hi all,

I have data set of three columns, name, year and age. My purpose is to fill up age record based on year.

The data set looks like,

Name year age
A 1998
A 1999
A 2000 44
A 2001
B 1996 36
B 1997
B 1998
..........................
C 1999
C 2000 38
C 2002
D 1994 33
D 1995 34
D 1996
D 1997

Here is the thing. For a specific person, I can always identify his/her age by year by one or more records, like A, if he is 44 in 2000, then in 1998 and 1999, he should be 42 and 43, respectively.
The problem is that; the identifier (44 in A's case) can show up irregularly. In A's case, it shows up at the 3rd record; in B's case, the age identifier shows up at the 1st place.
Another problem is that, year record can skip. In C's case, it goes from 1999, 2000, and then skip 2001, jumping to 2002 directly.

Basically, I want my data look like,

Name year age
A 1998 42
A 1999 43
A 2000 44
A 2001 45
B 1996 36
B 1997 37
B 1998 38
..........................
C 1999 37
C 2000 38
C 2002 39
D 1994 33
D 1995 34
D 1996 35
D 1997 36

Please help and many thanks!

Re: Fill age by condition

Vasile01 — Wed, 23 Feb 2011 15:25:08 GMT

Hi,

You provided a part of the desired outcome as:
.........................
C 1999 37
C 2000 38
C 2002 39

Is that correct?
Warm regards,
Vasile

Re: Fill age by condition

deleted_user — Wed, 23 Feb 2011 15:56:31 GMT

Hello,

On solution may be merging the data with itself:

[pre]
data test;
infile datalines missover;
input Name $ year age;
datalines;
A 1998
A 1999
A 2000 44
A 2001
B 1996 36
B 1997
B 1998
C 1999
C 2000 38
C 2002
D 1994 33
D 1995 34
D 1996
D 1997
;

data out;
merge test (drop=age) test(where=(^missing(_age)) rename=(age=_age year=_year));
by name;
if first.name then do born=_year-_age;
age=year-born;
end;

else age+1;

drop born _age _year;

run;
[/pre]

Marius

Re: Fill age by condition

deleted_user — Wed, 23 Feb 2011 16:11:38 GMT

My bad.

It should be 40 for 2002.

Re: Fill age by condition

Ksharp — Thu, 24 Feb 2011 01:53:14 GMT

It looks like Cartesian Product is helpful for you.

[pre]
data test;
infile datalines missover;
input Name $ year age;
datalines;
A 1998
A 1999
A 2000 44
A 2001
B 1996 36
B 1997
B 1998
C 1999
C 2000 38
C 2002
D 1994 33
D 1995 34
D 1996
D 1997
;
run;
data result;
declare hash hh(hashexp: 10 );
hh.definekey('name');
hh.definedata('_year','_age');
hh.definedone();

do until(last);
set test(rename=(year=_year age=_age)) end=last;
if not missing(_age) then hh.replace();
end;

do until(_last);
set test end=_last;
call missing(_year,_age);
hh.find();
if missing(age) then age=_age + ( year - _year);
output;
end;
drop _:;
run;

[/pre]

Ksharp

Re: Fill age by condition

Vasile01 — Thu, 24 Feb 2011 05:38:44 GMT

Hi,

Here it is an idea using SQL statements:

proc sql;

create table temp as
select name, year-age as yb from person
where age is not missing;

create table temp2 as
select distinct name, yb from temp;

update person
set age=year-(select yb from temp2
where name=person.name
)
where age is missing
;
quit;

Warm regards,
Vasile

Re: Fill age by condition

Ksharp — Thu, 24 Feb 2011 06:34:32 GMT

Or the following code is more readable ,But it is not suited for large dataset.

[pre]
data test;
infile datalines missover;
input Name $ year age;
datalines;
A 1998
A 1999
A 2000 44
A 2001
B 1996 36
B 1997
B 1998
C 1999
C 2000 38
C 2002
D 1994 33
D 1995 34
D 1996
D 1997
;
run;
proc sort data=test;
by name;
run;
data result;
merge test test(rename=(year=_year age=_age) where=(_age is not missing));
by name;
if missing(age) then age=_age + (year - _year);
drop _:;
run;

[/pre]

Ksharp

Re: Fill age by condition

deleted_user — Thu, 24 Feb 2011 08:27:59 GMT

hello,

I changed my code as you made a correction to the output data:

[pre]
data out;
merge test (drop=age) test(where=(^missing(_age)) rename=(age=_age year=_year));
by name;

retain born;

if first.name then do born=_year-_age;
age=year-born;
end;

else age=year-born;

drop born _age _year;

run;
[/pre]

Marius

Re: Fill age by condition

Ksharp — Thu, 24 Feb 2011 09:34:37 GMT

Hi.