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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.