How to find past data to identify whether the person has work experience in that year

[shawnchen0321]

Hello experts

I have a dataset (experience) that contains everyone's all work experience, including the start and end years of the job, and whether the job is related to IT majors.

data experience;
input Person$ Startyear Endyear IT_PROFESSION ;
;
cards;
A 2015 2017 0
A 2017 2018 0
A 2019 2019 0
A 2021 2021 0
A 2022 2022 1
A 2023 2023 0
B 2018 2022 1
B 2018 2022 0
B 2023 2023 0
;
/*B works in two different companies at the same time*/ 
run;

In addition, for a dataset (work), I want to combine it with a past dataset (have) to identify whether a person had an IT major before a specific year.

data work;
input Person$ YEAR ;
;
cards;
A 2019
A 2023
B 2023
;
run;

I hope it will eventually become the dataset (want).

data want;
input Person$ YEAR IT_EXPERTISE;
;
cards;
A 2019 0
A 2023 1
B 2023 1
;
run;

Thanks to all the experts.

[PaigeMiller]

Please explain the logic used that results in the final data set. The years in data set WORK do not appear to match the years in data set EXPERIENCE for each person.

[shawnchen0321]

Hello, expert:

The first data set mainly records all the work records of a specific person (including work years and work experience).
The second data set is that the specific person was a member of the board of directors in that year.

I want to know whether the particular person had relevant work experience before becoming a board member, which is why the second data set will have more recent years.

Thanks.

[andreas_lds]

Another question:

Another obs for experience

C 2018 2022 1

 and in work

C 2024

What is the expected result?

[shawnchen0321]

Thanks to the experts for pointing out my problem.

I have revised the data set (experience). It should include the following board work experience. If his work at that time was related to IT profession, then it is 1 (IT_PROFESSION).

The final data set (want), is that I want to identify whether a particular person had IT work experience before serving as a board member that year (IT_EXPERTISE = 1).

[PaigeMiller · Accepted solution]

proc summary data=experience nway;
    class person;
    var it_profession;
    output out=year(drop=_type_ _freq_) max=max_it_profession maxid(it_profession(startyear))=startyear_it_profession;
run;
data final;
    merge work year;
    by person;
    if year>=startyear_it_profession and max_it_profession=1 then it_expertise=1;
    else it_expertise=0;
run;

[shawnchen0321]

@PaigeMiller Amazing, the code worked. Thank you very much.