topic Re: Difference between dates excluding weekends in SAS Enterprise Guide

Difference between dates excluding weekends

CTT53 — Wed, 22 May 2019 13:28:14 GMT

Hey folks

I am have read and tried many of the suggestions for the time between 2 dates excluding weekends and have been unsuccessful. I am a beginner SAS user and all of my data has been done in query builder. I have a min date and a max date and I am looking at getting the difference between the dates excluding weekends. Can this be done in query builder? I do not want to have to export to excel to do this.

Thanks for any help you can provide.

Re: Difference between dates excluding weekends

tomrvincent — Wed, 22 May 2019 13:35:20 GMT

I use a variant of this:
https://blogs.sas.com/content/sasdummy/2011/05/09/calculating-the-number-of-working-days-between-two-dates/

Re: Difference between dates excluding weekends

data_null__ — Wed, 22 May 2019 13:35:32 GMT

Did you try the WEEKDAY interval.

 55         
 56         data _null_;
 57         x = today();
 58         y = '01May2019'd;
 59         format x y date11.;
 60         d1 = intck('DAY',y,x);
 61         d2 = intck('WEEKDAY',y,x);
 62         put _all_;
 63         run;
 
 x=22-MAY-2019 y=01-MAY-2019 d1=21 d2=15

Re: Difference between dates excluding weekends

PeterClemmensen — Wed, 22 May 2019 13:36:28 GMT

data _null_;
   mindate='22may2019'd;
   maxdate='31dec2019'd;
   days=intck('WEEKDAY',mindate,maxdate);
   put days=;
run;

Re: Difference between dates excluding weekends

CTT53 — Wed, 22 May 2019 14:33:57 GMT

can this be done in a computed column in query builder

Re: Difference between dates excluding weekends

Patrick — Thu, 23 May 2019 12:05:25 GMT

@CTT53

Yes. Just try it.

INTCK() is a function like any other.

Re: Difference between dates excluding weekends

CTT53 — Thu, 23 May 2019 12:10:36 GMT

I have tried

intck('weekday',t2.'Min Pre-Underwriting'n,t2.'Max Pre-Underwriting'n)

and this does not work. The computed column is empty

Re: Difference between dates excluding weekends

Patrick — Thu, 23 May 2019 23:08:08 GMT

@CTT53 wrote:

I have tried

intck('weekday',t2.'Min Pre-Underwriting'n,t2.'Max Pre-Underwriting'n)

and this does not work. The computed column is empty

@CTT53

What does the SAS log tell you? Any messages about missing values?

The function works, something with your data must be different than expected.

Below EG QueryBuilder generated code that returns expected values. What you set up needs to look similar.

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_HAVE AS 
   SELECT t1.Min_Pre_Underwriting, 
          t1.Max_Pre_Underwriting, 
          /* weekdays */
            (intck('weekday',t1.Min_Pre_Underwriting,t1.Max_Pre_Underwriting)) FORMAT=best32. AS weekdays, 
          /* days */
            (t1.Max_Pre_Underwriting-t1.Min_Pre_Underwriting) FORMAT=best32. AS days
      FROM WORK.HAVE t1;
QUIT;

Re: Difference between dates excluding weekends

TomKari — Fri, 24 May 2019 00:02:21 GMT

I just replicated @Patrick's code, and it works fine for me.

One thought; if you're using datetime values, not dates, that will mess things up.

Here's a little snippet of code that sets up some test data for you to play with. Just drop it into a code window and run it.

Tom

data Have;
	format Max_Pre_Underwriting Min_Pre_Underwriting date.;
	Max_Pre_Underwriting = '01feb2019'd;
	do Min_Pre_Underwriting = '01dec2017'd to '31jan2019'd;
		output;
	end;
run;

Re: Difference between dates excluding weekends

Patrick — Fri, 24 May 2019 00:43:58 GMT

@CTT53

Here the data step I've used to create sample data for the EG QueryBuilder

data have;
  format Min_Pre_Underwriting Max_Pre_Underwriting date9.;
  Min_Pre_Underwriting='01jan2019'd;
  do Max_Pre_Underwriting=Min_Pre_Underwriting to today() by 4;
    output;
  end;
  stop;
run;

And the reason I've also added below days calculation to the query:

(t1.Max_Pre_Underwriting-t1.Min_Pre_Underwriting) FORMAT=best32. AS days

If this returns missings then you know you get unexpected results because you're dealing with missings from source.

If you get very wrong numbers (like way too big) then you know you're dealing with something else than SAS Date values.

Re: Difference between dates excluding weekends

CTT53 — Fri, 31 May 2019 12:27:42 GMT

I do not get any errors when with the string. The results are blank

Re: Difference between dates excluding weekends

Patrick — Fri, 31 May 2019 14:02:32 GMT

@CTT53 wrote:

I do not get any errors when with the string. The results are blank

Something with your data must be different than expected.

What does the SAS log tell you? Any Notes about creating missing values?

May be share the relevant bit of the SAS log with us. May be also share with us the result of a Proc Contents for your source table.