topic Re: Merge observations based on age being less than or equal to in SAS Programming

Merge observations based on age being less than or equal to

MB_Analyst — Tue, 18 Dec 2018 23:21:23 GMT

I have two datasets:

DATA master;
    INPUT ID age age_class;
DATALINES;
1 30 0 
1 40 1
1 42 2 
2 25 1 
2 36 2
;

DATA records;
    INPUT ID age_record;
DATALINES;
1 21 
1 22 
1 31 
1 32 
1 41 
2 30  
2 32 
2 34 
;

I want to merge the age_class of master datafile into the records datafile based on ID and age_record being less than or equal to age.

The final dataset should look like:

ID age_record age_class

1 21 0

1 22 0

1 31 1

1 32 1

1 41 2

2 30 2

2 32 2

2 34 2

Re: Merge observations based on age being less than or equal to

Andygray — Tue, 18 Dec 2018 21:59:50 GMT

@MB_Analyst Can you please elaborate the logic to make us understand better?

Re: Merge observations based on age being less than or equal to

MB_Analyst — Tue, 18 Dec 2018 23:13:10 GMT

My end goal is to count the number of records for each person by ID and age_class. I will try to create a picture

Imagine we want to count the number of logins up until a point of time

Person One, where t=age or time

------->age_record=21--------->age_record=22---------> age_class=0& age=30 -------->age_record=31-------->age_record=32---->age_class=1& age=40

21 0

22 0

31 1

32 1

Does that make things more or less clear?

Re: Merge observations based on age being less than or equal to

novinosrin — Tue, 18 Dec 2018 22:47:11 GMT

@MB_Analyst Wouldn't the ageclass be 2 for 30 and 32?

2 30 a .

2 32 b .

2 34 c 2

Considering all fall below 36

And there is no obs 1 32 d 1 in your original sample, but is there in output. Would be nice if you could correct the needful

Re: Merge observations based on age being less than or equal to

novinosrin — Tue, 18 Dec 2018 23:07:00 GMT

@MB_Analyst Should my understanding and assumption be correct, it's pretty straight forward-->

DATA master;
    INPUT ID age age_class;
DATALINES;
1 30 0 
1 40 1
1 42 2 
2 25 1 
2 36 2
;

DATA records;
    INPUT ID age_record varx $;
DATALINES;
1 21 a
1 22 b
1 31 c 
1 32 d
1 32 e
1 41 f
2 30 a 
2 32 b
2 34 c
;

proc sql;
create table want(drop=dif ) as
select a.*,age_class,ifn(age-age_record<0,.,age-age_record) as dif
from records a left join  master b
on a.id=b.id  and age_record<=age
group by a.id,age_record
having min(dif)=dif
order by a.id,age_record;
quit;

Re: Merge observations based on age being less than or equal to

MB_Analyst — Tue, 18 Dec 2018 23:20:15 GMT

I edited the question and my posts to be more clear. Thank you