Replace name with latest entry

[shiv999]

Hi guys
My data is like
Id. Name $ 10 Doj :date9. dot:date9.;
11235 Jim 12jul2017 01auj2018
11235 Jim. 12jul2019 01auj2019
11235 Jimcarod 12jan2020 01feb2020
11235 Jimcarod 12jul2020 01auj2020

My code should replace all the row with name Jimcarod . As its is the latest entry and have same id

[PaigeMiller]

---

@shiv999 wrote:

My code should replace all the row with name Jimcarod . As its is the latest entry and have same id

---

Please explain this further. Replace what variables with what values in which rows?

[shiv999]

Replace 1st and 2nd row name column with name in 4th row as it doj date is lastest

[Amir]

Hi @shiv999,

Please show what all the rows of data should look like after the code runs with an explanation of the rules being applied.

Also if you have tried any code then please share that using the "Insert SAS Code" icon and any log issues using the "Insert Code" icon.

Kind regards,

Amir.

[shiv999]

After code changes it should look
Id Name $ 10 Doj :date9. dot:date9.;
11235 Jimcarod 12jul2017 01auj2018
11235 Jimcarod 12jul2019 01auj2019
11235 Jimcarod 12jan2020 01feb2020
11235 Jimcarod 12jul2020 01auj2020

I was planning with first. And last dot any suggestions

[novinosrin]

Can you please create a proper dataset that the community can use or would it be possible for you to provide in the form of neat tabulated format pasted from excel. 

[shiv999]

Hi thanks so much for your response I have attached the Excel and mentioned before and after changes

[novinosrin · Accepted solution]

Thank you @shiv999  Much better. Also, going forward paste the excel here as text rather than attachments-

data have;
input ID 	NAME & $15.	(DateofJoining	DateofTermination) (:date9.);
format DateofJoining	DateofTermination date9.;
cards;
112301	Jim	   1-Jul-17	1-Oct-17
112301	Jim	   1-Nov-17	1-Feb-18
112301	Jim carado	  1-Feb-18	1-Sep-19
112301	Jim carado	  1-Oct-20	1-Nov-20
112302	Ben	   1-Oct-19	2-Nov-20
112302	Ben tamar	 1-Mar-20	3-Nov-20
112303	Lamar	  1-Jun-19	1-May-20
112303	Lamar	  1-Oct-20	1-Nov-20
;

data want;
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
 end;
 _name=name;
 do _n_=1 to _n_;
  set have;
  name=_name;
  output;
 end;
 drop _name;
run;

[shiv999]

Thanks you so much , can we do with firstdot And last dot ?

[novinosrin]

HI Since you are looking for last name as the value to be used as replacement value, you will need only last dot

so-

data have;
input ID 	NAME & $15.	(DateofJoining	DateofTermination) (:date9.);
format DateofJoining	DateofTermination date9.;
cards;
112301	Jim	   1-Jul-17	1-Oct-17
112301	Jim	   1-Nov-17	1-Feb-18
112301	Jim carado	  1-Feb-18	1-Sep-19
112301	Jim carado	  1-Oct-20	1-Nov-20
112302	Ben	   1-Oct-19	2-Nov-20
112302	Ben tamar	 1-Mar-20	3-Nov-20
112303	Lamar	  1-Jun-19	1-May-20
112303	Lamar	  1-Oct-20	1-Nov-20
;

data temp;
 set have;
 by id;
 if last.id;
 _name=name;
 keep id _name;
run;

data want;
 merge have temp;
 by id;
 name=_name;
 drop _name;
run;

[Amir]

Hi @shiv999,

Thanks for the additional information. As @novinosrin has said text is better than attachments as some of us are prevented from downloading attachments for security reasons, which is very sensible.

If the data is not always sorted by doj within id then you could use something like the following.

data have;
   input
      id   : 8.
      name : $char10.
      doj  : date9.
      dot  : date9.
   ;

   format
      doj date9.
      dot date9.
   ;

   datalines;
11235 Jim 12jul2017 01aug2018
11235 Jim. 12jul2019 01aug2019
11235 Jimcarod 12jan2020 01feb2020
11235 Jimcarod 12jul2020 01aug2020
;


data want(drop = latest:);
   length latest_name $ 10;

   do until (last.id);
      set have;
      by id;

      latest_doj  = max(latest_doj,doj);
      latest_name = ifc(latest_doj eq doj,name,latest_name);
   end;

   do until (last.id);
      set have;
      by id;

      name = latest_name;
      output;
   end;
run;

Kind regards,

Amir.