How to break down year interval data

[shawnchen0321]

Hello experts:

I have the original work year data, but they are presented as intervals, as described below.

data worktime;
input Firmid Personid Role$ Startyear Endyear ;
;
cards;
001 01 CEO 2016 2018
001 02 CFO 2018 2020
002 04 CEO 2016 2017
002 05 CFO 2022 2023
;
run;

I need panel data, broken down by year's work situation, as shown below.

data workdetail;
input Firmid Fyear Personid  Role$ ;
;
cards;
001 2016 01 CEO
001 2017 01 CEO
001 2018 01 CEO
001 2018 02 CEO
001 2019 02 CFO
001 2020 02 CFO
002 2016 04 CEO
002 2017 04 CEO
002 2022 05 CFO
002 2023 05 CFO
;
run;

Thanks for your assistance🙏.

[MarkusWeick · Accepted solution]

Hi @shawnchen0321 ,

you coul for example use a do-loop:

data worktime;
input Firmid Personid Role$ Startyear Endyear ;
;
cards;
001 01 CEO 2016 2018
001 02 CFO 2018 2020
002 04 CEO 2016 2017
002 05 CFO 2022 2023
;
run;

data workdetail;
	retain Firmid Fyear Personid Role;
	drop Startyear Endyear i;
	set worktime;
	format Firmid z3. Personid z2.;
	do i = Startyear to Endyear;
		Fyear = i;
		output;
	end;
run;

to get

[shawnchen0321]

It works perfectly, thank you very much😊.