topic Re: Replace name with latest entry in SAS Procedures

Replace name with latest entry

shiv999 — Wed, 04 Nov 2020 12:21:01 GMT

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

Re: Replace name with latest entry

PaigeMiller — Wed, 04 Nov 2020 12:23:14 GMT

@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?

Re: Replace name with latest entry

Amir — Wed, 04 Nov 2020 12:31:00 GMT

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.

Re: Replace name with latest entry

shiv999 — Wed, 04 Nov 2020 12:31:10 GMT

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

Re: Replace name with latest entry

shiv999 — Wed, 04 Nov 2020 12:42:16 GMT

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

Re: Replace name with latest entry

novinosrin — Wed, 04 Nov 2020 12:55:23 GMT

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.

Re: Replace name with latest entry

shiv999 — Wed, 04 Nov 2020 13:19:50 GMT

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

Re: Replace name with latest entry

novinosrin — Wed, 04 Nov 2020 13:26:04 GMT

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;

Re: Replace name with latest entry

shiv999 — Wed, 04 Nov 2020 13:37:58 GMT

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

Re: Replace name with latest entry

novinosrin — Wed, 04 Nov 2020 13:43:22 GMT

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;

Re: Replace name with latest entry

Amir — Wed, 04 Nov 2020 13:43:26 GMT

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.