Change a variable value to another datapoint's variable value based on another variable

[kelbalooz]

Hi everyone!

My question is a bit simpler than the subject makes it sound, but I can't figure it out.

Here's an idea of what I have:

ID	VisiteDate	DiagnosticDate
001	08/25/2021	06/03/2013
001	08/25/2022
001	08/25/2023
002	06/02/2022	05/06/2020
002	06/02/2023
003	02/23/2021	04/04/2018
003	02/23/2022
003	02/23/2023
004	05/14/2021	04/27/2001
004	05/14/2022
004	05/14/2023

And here's what I would like to have:

ID	VisiteDate	DiagnosticDate
001	08/25/2021	06/03/2013
001	08/25/2022	06/03/2013
001	08/25/2023	06/03/2013
002	06/02/2022	05/06/2020
002	06/02/2023	05/06/2020
003	02/23/2021	04/04/2018
003	02/23/2022	04/04/2018
003	02/23/2023	04/04/2018
004	05/14/2021	04/27/2001
004	05/14/2022	04/27/2001
004	05/14/2023	04/27/2001

Thanks in advance for the help!

[PaigeMiller]

/* UNTESTED CODE */
data want;
    set have;
    retain diagnosticdate1;
    if not missing(diagnosticdate) then diagnosticdate1=diagnosticdate;
run;

[kelbalooz]

No that doesn't seem to be working unfortunately.

[LinusH]

/* UNTESTED CODE */
data want;
    set have;
    retain diagnosticdate1;
    if not missing(diagnosticdate) then diagnosticdate1=diagnosticdate;
    else diagnosticdate=diagnosticdate1;
run;

[kelbalooz]

I ran that code too, it also doesn't appear to work

[Tom]

RETAINing a NEW variable works fine for your example data.

data have;
  input ID (VisiteDate DiagnosticDate) (:mmddyy.);
  format VisiteDate DiagnosticDate yymmdd10.;
cards;
001 08/25/2021 06/03/2013
001 08/25/2022 . 
001 08/25/2023 . 
002 06/02/2022 05/06/2020
002 06/02/2023 . 
003 02/23/2021 04/04/2018
003 02/23/2022 . 
003 02/23/2023 . 
004 05/14/2021 04/27/2001
004 05/14/2022 . 
004 05/14/2023 . 
;

data want;
  set have;
  by id VisiteDate;
  if first.id then want=DiagnosticDate;
  retain want;
  DiagnosticDate = coalesce(DiagnosticDate,want);
  output;
  want = DiagnosticDate;
  format want yymmdd10.;
run;

data expect;
  input ID (VisiteDate DiagnosticDate) (:mmddyy.);
  format VisiteDate DiagnosticDate yymmdd10.;
cards;
001 08/25/2021 06/03/2013
001 08/25/2022 06/03/2013
001 08/25/2023 06/03/2013
002 06/02/2022 05/06/2020
002 06/02/2023 05/06/2020
003 02/23/2021 04/04/2018
003 02/23/2022 04/04/2018
003 02/23/2023 04/04/2018
004 05/14/2021 04/27/2001
004 05/14/2022 04/27/2001
004 05/14/2023 04/27/2001
;

proc compare data=expect compare=want;
run;

The COMPARE Procedure
Comparison of WORK.EXPECT with WORK.WANT
(Method=EXACT)

Data Set Summary

Dataset               Created          Modified  NVar    NObs

WORK.EXPECT  25AUG23:10:16:16  25AUG23:10:16:16     3      11
WORK.WANT    25AUG23:10:16:16  25AUG23:10:16:16     4      11


Variables Summary

Number of Variables in Common: 3.
Number of Variables in WORK.WANT but not in WORK.EXPECT: 1.


Observation Summary

Observation      Base  Compare

First Obs           1        1
Last  Obs          11       11

Number of Observations in Common: 11.
Total Number of Observations Read from WORK.EXPECT: 11.
Total Number of Observations Read from WORK.WANT: 11.

Number of Observations with Some Compared Variables Unequal: 0.
Number of Observations with All Compared Variables Equal: 11.

NOTE: No unequal values were found. All values compared are exactly equal.

[andreas_lds]

---

@kelbalooz wrote:

No that doesn't seem to be working unfortunately.

---

If you want tested code, start by posting data in usable form.

Also explain "not working" as this can mean anything.

[mkeintz · Accepted solution]

Untested:

data want;
  merge have (drop=diagnosticdate)
        have (keep=id diagnosticdate where=(diagnosticdate^=.));
  by id ;
run;

This code assumes

1. There is only one diagosticdate per ID, no matter how many  visitdates that ID may have.
2. The data are sorted by ID.

[kelbalooz]

This one worked great! Thanks a ton mkeintz!