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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.