topic Re: difference between two dates - Days in SAS Enterprise Guide

difference between two dates - Days

bhca60 — Fri, 22 Jul 2022 08:49:00 GMT

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;

Re: difference between two dates - Days

andreas_lds — Fri, 22 Jul 2022 09:40:10 GMT

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;

Re: difference between two dates - Days

bhca60 — Fri, 22 Jul 2022 11:14:03 GMT

notedate is $CHAR11.
dischdate $10.

Re: difference between two dates - Days

PaigeMiller — Fri, 22 Jul 2022 11:17:35 GMT

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.

Re: difference between two dates - Days

bhca60 — Fri, 22 Jul 2022 11:25:34 GMT

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

Re: difference between two dates - Days

PaigeMiller — Fri, 22 Jul 2022 11:30:24 GMT

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;

Re: difference between two dates - Days

bhca60 — Fri, 22 Jul 2022 11:48:43 GMT

I did the above and i got this note:

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
28:21
NOTE: Invalid argument to function INPUT at line 28 column 15.

Re: difference between two dates - Days

PaigeMiller — Fri, 22 Jul 2022 12:04:17 GMT

A couple of things. Whenever there are issues in the log, we need to see the ENTIRE log for this DATA step, not selected parts of it. (And in the future, the same requirements hold). Copy the log as text and paste the log into the window that appears when you click on the </> icon.

We also need to see a portion of the data — please provide data with all of your questions from now on, in a usable form, which is either manually typed working SAS data step code, or using these instructions. Attachments and screen captures are not acceptable.

Please comply with these requests from now, don't make us keep asking.

Re: difference between two dates - Days

bhca60 — Wed, 27 Jul 2022 04:26:05 GMT

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

Re: difference between two dates - Days

SASKiwi — Wed, 27 Jul 2022 04:41:03 GMT

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;

Re: difference between two dates - Days

bhca60 — Wed, 27 Jul 2022 04:50:35 GMT

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.

Re: difference between two dates - Days

Kurt_Bremser — Wed, 27 Jul 2022 05:42:11 GMT

Always (as in ALWAYS) post the complete log of a step, so we can see the statements SAS is complaining about. Use this button to post the log:

Re: difference between two dates - Days

Kurt_Bremser — Wed, 27 Jul 2022 05:47:11 GMT

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.

Re: difference between two dates - Days

bhca60 — Wed, 27 Jul 2022 06:35:31 GMT

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;

Re: difference between two dates - Days

Kurt_Bremser — Wed, 27 Jul 2022 08:32:24 GMT

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.

Re: difference between two dates - Days

bhca60 — Wed, 27 Jul 2022 15:22:40 GMT

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

Re: difference between two dates - Days

jebjur — Wed, 27 Jul 2022 16:06:25 GMT

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.

Re: difference between two dates - Days

bhca60 — Wed, 27 Jul 2022 16:55:04 GMT

OMG! THANK YOU SOOOOO MUCH!!!