topic Re: Find number of days between two dates in SAS Programming

Find number of days between two dates

Moksha — Sat, 10 Jun 2023 12:15:51 GMT

Hi All,

I get dates in the IS8601da. format yymmdd10. and I need to find number of days, years, months into different variables.

For example, I have dates like Startdate = 1990-01-01 and Dt1 = 2012-02-01 and here I need to consider this Dt1 as end date. That means, I should get -8066.

I have tried to use INTCK, but it seems it works only with datew. format. With datew. format dates 01Jan90 and 01Feb12, INTCK is giving -8066. But , with yymmddw. format and it is giving 12.

Tried using datdiff with yymmddw. format dates, but it is giving difference as 12. When used datdiff with datew. format dates it is giving -8066.

Please, let me know how to find the difference between dates that are in yymmdd10. formats.

Thanks

Re: Find number of days between two dates

PaigeMiller — Sat, 10 Jun 2023 14:02:24 GMT

Using INTCK on any valid SAS date values should work with ANY date format, or for unformatted dates. Using DATDIF on any valid SAS date values should work with ANY date format, or unformatted dates.

data abc;
    firstdate=mdy(1,1,1990);
    seconddate=mdy(2,1,2012);
    format firstdate seconddate yymmdd10.;
    delta=intck('day',seconddate,firstdate);
    delta1=datdif(seconddate,firstdate,'act/act');
    delta2=firstdate-seconddate;
run;

Please note that for valid SAS dates, subtraction also works to find the number of days between two dates, and is the least typing of any method.

If you are getting wrong answers, I suspect your variables do not have valid SAS date values. Please provide (a portion of) your SAS data set (as working SAS data step code, and not in any other format) AND your code that is giving wrong answers.

Re: Find number of days between two dates

Kurt_Bremser — Sat, 10 Jun 2023 13:08:37 GMT

In SAS, a date is a date is a date, regardless of display format. So the INTCK or DATEDIFF functions will work with them.

You only need to use the correct informat when reading the data into SAS in the first place.

We need more information. Where is the data coming from, and in which form (file format) do you get it?

Re: Find number of days between two dates

Moksha — Sat, 10 Jun 2023 14:08:55 GMT

Thank you very much for the clarification. Actually, I don't have the code to create the dataset. I am working on a dataset that is already created and it has startDate and dt columns which are in the yymmdd10. format. I am using direct subtraction of these dates and it's giving expected values.

However, I want to check these dates if they are calculated correctly or not. Hence, I am trying to check giving the yymmdd10. date formats like this:

data testdt;
	startdt = 1990-01-01;
	dt = 2012-02-01;

	startdate = '01Jan90'd;
	sdt = '01Feb2012'd;

/*	informat startdt dt yymmdd10.;*/
	format startdt dt yymmdd10.;

	diff1 = startdt - dt;

	diff2 = datdif(startdt, dt, 'ACT/ACT');

	diff3 = intck('day',startdt, dt);

	diff4 = datdif(sdt, startdate, 'ACT/ACT');

	diff5 = intck('day', sdt, startdate);

run;

Re: Find number of days between two dates

Moksha — Sat, 10 Jun 2023 14:13:26 GMT

Thank you for the clarification. I am working on a dataset that is already created and I don't have the code that created it. It has columns which are numeric and contain date values in yymmdd10. format and I have to find the difference between them. I get these columns into a macro variable and doing direct subtraction of these variables and it is giving correct values.

But, for my understanding, I am trying to check whether the values are correct or not and I am tryin using the code that I have mentioned in reply to PaigeMiller.

Re: Find number of days between two dates

mkeintz — Sat, 10 Jun 2023 15:41:41 GMT

Your first statements

	startdt = 1990-01-01;
	dt = 2012-02-01;

are NOT establishing date values. Instead, they are calculating arithmetic expressions on the right side of the equals sign and assigning the result to the variable on the left.

You probably need to use a date literal, as you did in

        startdate = '01Jan90'd;
        sdt = '01Feb2012'd;

The above is NOT a date format. It has nothing to do with the format (i.e. how a value is displayed). You can assign any date format to a date value as below.

Added comment: And has nothing to do with how a variable is input from an external source. It's just a way to assign, within a SAS program, a constant value to be converted to SAS date value s (zero for 01jan1960, negative before that, and positive afterwards). It's not unlike using the comma informat to remove commas when reading a numeric value (or comma format to add commas for display).

931  data want;
932    startdate='01jan1990'd;
933    sdt='01feb2012'd;
934    put (_all_) (=yymmdd10.);
935    put (_all_) (=date9.);
936  run;

startdate=1990-01-01 sdt=2012-02-01
startdate=01JAN1990 sdt=01FEB2012
NOTE: The data set WORK.WANT has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Now, if you are reading in date values from a text file, you SHOULD use the appropriate informat as in

data want;
  input (startdate sdt)  (:yymmdd10.);
   put (_all_) (=yymmdd10.);
   put (_all_) (=date9.);
datalines;
1990-01-01  2012-02-01
run;

Finally if you want to use expressions such as 1990-01-01 as constants to be converted to date values, you could use the INPUT function:


data want;
  startdate=input('1990-01-01',yymmdd10.);
  sdt=input('2012-02-01',yymmdd10.);
  put (_all_) (=yymmdd10.);
  put (_all_) (=date9.);
run;

Re: Find number of days between two dates

PaigeMiller — Sat, 10 Jun 2023 15:37:58 GMT

@Moksha wrote:

Thank you very much for the clarification. Actually, I don't have the code to create the dataset.

I am requesting that you TYPE IN yourself a small portion of the data, into working SAS data step code. Preferably, you can also follow these instructions to produce working SAS data step code. We need a properly produced replicate of your existing SAS data set.

However, I want to check these dates if they are calculated correctly or not. Hence, I am trying to check giving the yymmdd10. date formats like this:

	startdt = 1990-01-01;
	dt = 2012-02-01;

These are not anything that SAS recognizes as a date. The fact that humans recognize this is a date is irrelevant. SAS consider STARTDT to be the integer 1990 minus 01 minus 01, which equals 1988. SAS considers DT to be 2012 minus 02 minus 01 which equals 2009. These are clearly not dates, and so any arithmetic or use of INTCK/INTNX/DATDIF will fail on these variables.

	startdate = '01Jan90'd;
	sdt = '01Feb2012'd;

By using this syntax, then SAS recognizes these as dates and so then arithmetic and INTCK/INTNX/DATDIF will work properly. The above is is not a DATE9. format, it is a date literal. This is so important, I am going to say it again in big bold capital letters for emphasis. THIS IS NOT A DATE9. FORMAT, IT IS A DATE LITERAL. A date literal is one way that humans can communicate what they see as a date to SAS, it translates human understanding to SAS understanding; just as if you speak English, you need Japanese to be translated in order for the English speaking person to understand it; a date literal does translation.

Re: Find number of days between two dates

ballardw — Sat, 10 Jun 2023 16:17:52 GMT

@Moksha wrote:

Thank you for the clarification. I am working on a dataset that is already created and I don't have the code that created it. It has columns which are numeric and contain date values in yymmdd10. format and I have to find the difference between them. I get these columns into a macro variable and doing direct subtraction of these variables and it is giving correct values.

But, for my understanding, I am trying to check whether the values are correct or not and I am tryin using the code that I have mentioned in reply to PaigeMiller.

Run Proc contents on the data set and show us the result.

Proc contents data=yourdataset;
run;

Copy the results and paste into the forum window.

With an accurate description of the variable properties and example values we can provide the approach to make the values dates.

SAS has something like 28 provided ISO date, time and datetime formats and 17 Informats to read external files using ISO layouts to read into date, time or datetime values.

When talking about SAS you should ONLY ever use the word "format" as it relates to the setting of the format property for a variable. External data sources don't have "formats" in this context, layout perhaps but not the assigned property that SAS calls a format.

About once a week or so we will get someone that has created or inherited a variable that is numeric and the content is something like 20210615 or 15062021 or (worse) 6152021 that masquerades as a "date". This is quite common when using Proc Import or an import task to read external data.

Re: Find number of days between two dates

Moksha — Sat, 10 Jun 2023 17:11:34 GMT

Thank you very much. Now, I understand why my code where I am checking for the dates using datdif and intck functions are not working as expected.

Re: Find number of days between two dates

Moksha — Sat, 10 Jun 2023 17:17:45 GMT

I have already provided the dataset which I am trying to check the date differences using datdif, intck and direct subtraction. With your explantion and mkeintz's explanation, I understand the problem with my checking of date differences using datdiff and intck.

Thank you very much.