Difference between dates excluding weekends

[CTT53]

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.

[tomrvincent]

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

[data_null__]

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

[PeterClemmensen]

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

[CTT53]

can this be done in a computed column in query builder

[Patrick]

@CTT53 

Yes. Just try it.

INTCK() is a function like any other.

[CTT53]

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

[Patrick]

---

@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;

[CTT53]

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

[Patrick]

---

@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.

[TomKari]

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;

[Patrick]

@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.