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

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;

01.JPG

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.

02.JPG

How should I achieve it?

Thank you all.

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

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;

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ErikLund_Jensen
Rhodochrosite | Level 12

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
Obsidian | Level 7

It works.

Big thanks for your help.

Very appreciate.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 3 replies
  • 477 views
  • 2 likes
  • 3 in conversation