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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.