topic Re: Collapsing rows into one row and overwriting a column in SAS Programming

Collapsing rows into one row and overwriting a column

BrinaLi — Sun, 02 Apr 2023 07:22:11 GMT

Hello!

I am working with a dataset that has employee start and end dates. I want to collapse this employ table into one current row and overwrite the beginning date with the first date provided of the year in the begin_date column

Here's a picture of what I'd like to do:

I am working with over a million rows and would like to narrow it down to 2023, but hoping to be able to use this same code going into future years.

Can anyone suggest how to do this?

thank you!

Re: Collapsing rows into one row and overwriting a column

Kurt_Bremser — Sun, 02 Apr 2023 08:02:55 GMT

%let year = 2023;

data want;
set have;
by employee_id;
retain _start;
if first.employee_id then _start = .;
if _start =. and year(begin_date) = &year. then _start = begin_date;
if last.employee_id;
if _start ne . then begin_date = _start;
drop _start;
run;

Re: Collapsing rows into one row and overwriting a column

Tom — Sun, 02 Apr 2023 15:45:59 GMT

Can you explain what "narrow it down to 2023" means?

Do you want to only keep the observations where the end_date is greater than '01JAN2023'd ?

Or did you have some other interpretation in mind?

Re: Collapsing rows into one row and overwriting a column

BrinaLi — Sun, 02 Apr 2023 23:29:09 GMT

thank you! this worked!!!

Re: Collapsing rows into one row and overwriting a column

BrinaLi — Sun, 02 Apr 2023 23:32:00 GMT

sure! I meant to only work with employee begin dates that started in 2023. Sorry for the confusion! I wanted to keep the most recent observation, it has an end date of 31-dec-99, but insert the earliest date that started in 2023 for the employee as the begin date for that row.