Summarize the overlap data

[shawnchen0321]

Hi everyone, I have a question.

The raw data shows that there are two people working in different companies, during their working time they also service in others company. In addition, the data shows the overlap-start time and overlap-end time.

Here is the raw data.

data have;
input PERSON $ 	WORK_MAIN $ START END	 SERVICE $ START END	 OVERLAP_START OVERLAP_END
;
cards;
A	AAPL 2005	2012	 TMU 2007 2012 2007 2012
A	AAPL 2005	2012	 ABC 2009 2012 2009 2012
B	TSMC 2018	2022	 TMU 2019 2022 2019 2022
B	TSMC 2018	2022	 ABC 2013 2022 2018 2022
;
proc print;
run;

The final result I want shows below. It summarized during the time when person A worked in AAPL, he did service in one company in 2007 and in two companies in 2009.

How should I achieve it?

Thank you all.

[PaigeMiller]

Please provide the data as working SAS data step code, as you did in your previous thread.

[ErikLund_Jensen · Accepted solution]

Hi @shawnchen0321 

Your data step is not working as expected, because you use the same variable names START and END for two different columns. They have to be unique.

Also, there is no need to include the OVERLAP columns, as they don't supply any extra information.

Here is a two step solution, where all records are rolled out from Main_Start to Main_End with a flag set to 1, if fthe actual year is within the Service time span, and the output is finally summarized.

data have;
input Person$ Main$ Main_Start Main_End Service$ Service_Start Service_End;
;
cards;
A	AAPL 2005	2012	 TMU 2007 2012 2007 2012
A	AAPL 2005	2012	 ABC 2009 2012 2009 2012
B	TSMC 2018	2022	 TMU 2019 2022 2019 2022
B	TSMC 2018	2022	 ABC 2013 2022 2018 2022
;
run;

* Roll out;
data temp (keep=Person Year Servicekey); 
  set have;
  do Year = Main_Start to Main_End;
    if Service_Start <= Year <= Service_End then Servicekey = 1;
    else Servicekey = 0;
    output;
  end;
run;

* Summarize;
proc sql;
  create table want as
    select distinct
      Person,
      Year, 
      sum(Servicekey) as Service
    from temp
    group by 
      Person,
      Year;
quit;

[shawnchen0321]

It works.

Big thanks for your help.

Very appreciate.