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
@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;
@MB_Analyst Can you please elaborate the logic to make us understand better?
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
login age_class
21 0
22 0
31 1
32 1
Does that make things more or less clear?
@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
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.