BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kelbalooz
Calcite | Level 5

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:

IDVisiteDateDiagnosticDate
00108/25/202106/03/2013
00108/25/2022 
00108/25/2023 
00206/02/202205/06/2020
00206/02/2023 
00302/23/202104/04/2018
00302/23/2022 
00302/23/2023 
00405/14/202104/27/2001
00405/14/2022 
00405/14/2023 

 

 

And here's what I would like to have:

IDVisiteDateDiagnosticDate
00108/25/202106/03/2013
00108/25/202206/03/2013
00108/25/202306/03/2013
00206/02/202205/06/2020
00206/02/202305/06/2020
00302/23/202104/04/2018
00302/23/202204/04/2018
00302/23/202304/04/2018
00405/14/202104/27/2001
00405/14/202204/27/2001
00405/14/202304/27/2001

 

Thanks in advance for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26
/* UNTESTED CODE */
data want;
    set have;
    retain diagnosticdate1;
    if not missing(diagnosticdate) then diagnosticdate1=diagnosticdate;
run;
--
Paige Miller
kelbalooz
Calcite | Level 5

No that doesn't seem to be working unfortunately.

LinusH
Tourmaline | Level 20
/* UNTESTED CODE */
data want;
    set have;
    retain diagnosticdate1;
    if not missing(diagnosticdate) then diagnosticdate1=diagnosticdate;
    else diagnosticdate=diagnosticdate1;
run;
Data never sleeps
kelbalooz
Calcite | Level 5
I ran that code too, it also doesn't appear to work
Tom
Super User Tom
Super User

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
Jade | Level 19

@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
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
kelbalooz
Calcite | Level 5

This one worked great! Thanks a ton mkeintz!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 915 views
  • 4 likes
  • 6 in conversation