difference between two dates - Days

[bhca60]

I want to get the difference between dischdate and notedate and grab the data with datediff between -3 and 3 days. How do I do this?

proc sql;
create table want
as select 
c.fname,
c.dischdate,
d.fname,
d.notedate
from have1 c inner join have2 d
on c.fname=d.fname;
quit;

[andreas_lds]

Please post the data you have in usable form.

Do you have proper dates or strings?

The code ist, of course, untested.

data want;
  merge have1(keep=fname dischdate in=in1) have2(keep= fname notedate in=in2);
  by fname;
  if in1 and in2;
  if -3 <= intck('day', dischdate, notedate) <= 3;
run;

[bhca60]

notedate is $CHAR11.
dischdate $10.

[PaigeMiller]

These are character variables, and cannot be treated as dates in SAS (even if they look like dates to humans). You cannot subtract one character variable from another character variables.

These will have to be converted to valid SAS dates, which are numeric values equal to the number of days since 01JAN1960.

Please show us examples of how both of these variables appear to you.

[bhca60]

2021-10-15 - this is how it looks for both notedate and dischdate

[bhca60]

Still trying to get the difference between the dischdate and notedate so that it outputs the number of days difference.

The data I have is:

have1:
fname           dischdate
joe morris    2021-10-14
john doe      2021-11-15

have2:
fname          notedate
joe morris    2021-10-17
john doe      2021-11-17

The fname is a character.
The dischdate is a numeric; length 8; format YYMMDD10.
The notedate is a character; length 11; format $CHAR11

[SASKiwi]

A modified version of @PaigeMiller 's program should work in that case since dischdate is already a SAS date:

data want;
  merge have1(keep=fname dischdate in=in1) have2(keep= fname notedate in=in2);
  by fname;
  if in1 and in2;
  notedate_n=input(notedate,yymmdd10.);
  if -3 <= intck('day', dischdate, notedate_n) <= 3;
run;

[bhca60]

I did what you told me but the output for notedate_n looks like this:
22539, 22735, etc. they are all 5 digit numbers

Also, I wanted to create a column where the difdays populate.

[Kurt_Bremser]

Values like these are most likely valid SAS date values (count of days from 1960-01-01) and need only a proper fornat to become human-readable.

Show your complete code and log where you try to create difdays.

[bhca60]

23         data have3;
24         merge have1 (keep=fname dischdate in=ina)
25         	 have2(keep=fname notedate in=inb);
26         by fname;
27         if in1 and in2;
28         notedate_n=input(notedate,yymmdd10.);
29         if -3 <= intck('day', dischdate, notedate_n) <= 3;
30         run;
NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      115 at 29:10 

I wanted to create a column called difdays doing a formula like this: difdays=intck('day', dischdate, notedate_n)   then do  something like : if -3 <= difdays <= 3;

[Kurt_Bremser]

You did not post the complete log. It will contain a NOTE about uninitialized variables: you use in1 and in2 in the first subsetting if, but define ina and inb in the dataset options.

But if you have multiple observations for a given fname in both datasets, and want to find pairs wihich are within 3 days, you need to use SQL, as you must work from a cartesian join:

proc sql;
create table have3 as
  select
    have1.fname,
    have1.dischdate,
    input(have2.notedate,yymmdd10.) as notedate_n format=yymmdd10.,
    abs(notedate_n - dischdate) as difdays
  from have1 inner join have2
  on have1.fname = have2.fname
  having difdays le 3
  order by fname, dischdate
;
quit;

For more help, post examples of have1 and have2 as data steps with datalines; do not skip this.

[bhca60]

Everything ran fine, it's just that the notedate_n column brought back 5 digit numbers as explained in previous post.  Also, the log had no errors:

23         data have3;
24         merge have1 (keep=fname dischdate in=in1)
25         	 have2(keep=fname notedate in=in2);
26         by fname;
27         if in1 and in2;
28         notedate_n=input(notedate,yymmdd10.);
29         if -3 <= intck('day', dischdate, notedate_n) <= 3;
30         run;
NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      115 at 29:10   

I am trying to create a column for difdays that brings in anybody who has between -3 and 3 days difference between the dischdate and the notedate_n.  this is all. I didnt think it would be this complicated. I will try something else, thank you

[jebjur · Accepted solution]

You are on the right track. The character date string (NOTEDATE)  first needs to be converted to a numeric date value with the INPUT function. The 5-digit results you see are correct, a SAS date is the number of days since Jan 1, 1960. You need to apply a format to the date value so it displays properly. Once you convert the date, you can find the number of days between the two dates with the INTCK function, and then subset the table appropriately.

For example:

data have1;
infile cards dsd truncover;
input fname :$12. dischdate :yymmdd10.;
format dischdate yymmdd10.;
cards;
jane doe,2021-11-17
joe morris,2021-10-14
john doe,2021-11-15
mary jones,2021-11-12
tom brown,2021-12-02
;
run;

data have2;
infile cards dsd truncover;
input fname :$12. notedate :$11.;
cards;
jane doe,2021-11-15
joe morris,2021-10-17
john doe,2021-11-17
mary jones,2021-11-18
tom brown,2021-11-25
;
run;

data have3 (drop=notedate);
merge have1 (keep=fname dischdate in=ina)
have2(keep=fname notedate in=inb);
by fname;
if ina and inb;
notedate_n=input(notedate,yymmdd10.);
format notedate_n yymmdd10.;
datediff=intck('day', dischdate, notedate_n);
if -3 <= datediff <= 3;
run;

If you get any notes/errors/warnings with your code, please send in the entire log so we can better determine the issue. 

[bhca60]

OMG! THANK YOU SOOOOO MUCH!!!

[PaigeMiller]

I modify the solution from @andreas_lds to convert these character strings to actual SAS date values, via the INPUT function and the YYMMDD10 informat.

data want;
  merge have1(keep=fname dischdate in=in1) have2(keep= fname notedate in=in2);
  by fname;
  if in1 and in2;
  dischdate_n=input(dischdate,yymmdd10.);
  notedate_n=input(notedate,yymmdd10.);
  if -3 <= intck('day', dischdate_n, notedate_n) <= 3;
run;