BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MB_Analyst
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

@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;

View solution in original post

5 REPLIES 5
Andygray
Quartz | Level 8

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

MB_Analyst
Obsidian | Level 7

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?

 

novinosrin
Tourmaline | Level 20

@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
Obsidian | Level 7
I edited the question and my posts to be more clear. Thank you
novinosrin
Tourmaline | Level 20

@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;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1049 views
  • 3 likes
  • 3 in conversation