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!
Untested:
data want;
merge have (drop=diagnosticdate)
have (keep=id diagnosticdate where=(diagnosticdate^=.));
by id ;
run;
This code assumes
/* UNTESTED CODE */
data want;
set have;
retain diagnosticdate1;
if not missing(diagnosticdate) then diagnosticdate1=diagnosticdate;
run;
No that doesn't seem to be working unfortunately.
/* UNTESTED CODE */
data want;
set have;
retain diagnosticdate1;
if not missing(diagnosticdate) then diagnosticdate1=diagnosticdate;
else diagnosticdate=diagnosticdate1;
run;
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.
@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.
Untested:
data want;
merge have (drop=diagnosticdate)
have (keep=id diagnosticdate where=(diagnosticdate^=.));
by id ;
run;
This code assumes
This one worked great! Thanks a ton mkeintz!
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
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.
Ready to level-up your skills? Choose your own adventure.