Bring Back working days from 2 dates in 2 two columns

[dassuz]

Hi I am trying to bring back working days from two columns in SAS I used the following code

data Want;
WorkDays_Diff = intck ('Weekday',ReadDate, CreatedDate);
set have;
run;

How ever when i got the results Workdays_Diff comes one row below and theres a dot' .' in the first row. So the results for row 1 is in row 2 and result for row 2 is in row 3 etc

If you want to manipulate values after they are read from the data set on the Set statement the calculation should come AFTER the SET statement.

[Krueger]

Can you share your Have dataset?

[dassuz]

The Data Set comprises of 550000 rows, I have attached a small sample but I have amended the sensitive data

[dassuz]

Sample Data same as attachment

Src_Sys_Metering_Point_Num	MPXN1	SAP_Installation_Num	Meter_Reading_Doc_Id	ReadDate	Meter_Read	Meter_Reading_Type_Cd	Meter_Read_Rsn	CreatedDate	COR_flag	Date_Diff
14262	14262	6000373	39855	14/10/2019	54910	CO	1	14/10/2019	1	0
14262	14262	6000373	75953	15/09/2019	54910	CO	1	15/09/2019	1	0
14262	14262	6000373	7427	15/08/2019	54910	CO	1	15/08/2019	1	0
14262	14262	6000373	78100	15/07/2019	54910	CO	1	15/07/2019	1	0
12433	12433	6002031	40733	01/10/2019	746229	I	6	18/10/2019	0	17
12433	12433	6002031	30504	30/09/2019	0	1	21	30/09/2019	0	0
877578	877578	6002017	76059	30/09/2019	39330	1	1	17/10/2019	0	17
877578	877578	6002017	58531	30/09/2019	84386	1	1	17/10/2019	0	17
877578	877578	6002017	84569	30/09/2019	26480	3	1	04/10/2019	0	4
562763	562763	623333	46061	16/10/2019	0	R	9	17/10/2019	0	1
562763	562763	623333	74389	15/10/2019	769603	R	9	16/10/2019	0	1
562763	562763	623333	33734	15/10/2019	0	R	9	16/10/2019	0	1
562763	562763	623333	33452	15/10/2019	95563	R	9	16/10/2019	0	1
562763	562763	623333	38650	14/10/2019	749403	R	9	15/10/2019	0	1
562763	562763	623333	70710	14/10/2019	70934	R	9	15/10/2019	0	1

[PeterClemmensen]

Have you seen this?

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

[dassuz]

that is what I used but instead of putting the dates I entered the column dates

[unison]

It looks like your "date" columns may actually be character columns.

You need to work to convert the "dd/mm/yyyy" to a date format of your choice. Here, I convert to date9.

Try something like this:

data raw;
	input ReadDate $10. CreatedDate $10.;
	datalines;
14/10/201914/10/2019
15/09/201915/09/2019
15/08/201915/08/2019
15/07/201915/07/2019
01/10/201918/10/2019
30/09/201930/09/2019
30/09/201917/10/2019
30/09/201917/10/2019
30/09/201904/10/2019
16/10/201917/10/2019
;
run;

data want(drop=ReadDate CreatedDate rename=(ReadDate2=ReadDate CreatedDate2=CreatedDate));

	set raw;

	ReadDate = transtrN(ReadDate,"/",trimn(' '));
	CreatedDate = transtrN(CreatedDate,"/",trimn(' '));

	format ReadDate2 CreatedDate2 date9.;
	ReadDate2 = input(ReadDate,ddmmyy10.);
	CreatedDate2 = input(CreatedDate,ddmmyy10.);

	WorkDays_Diff = intck ('Weekday',ReadDate2, CreatedDate2);

run;

-unison

[Patrick]

@unison 

There is no need to convert the source string. Just use the informat with a defined length.

data _null_;
  dt=input('14/10/2019',ddmmyy10.);
  put dt= date9.;
  stop;
run;

28         data _null_;
29           dt=input('14/10/2019',ddmmyy10.);
30           put dt= date9.;
31           stop;
32         run;

dt=14OCT2019

[Patrick]

@dassuz 

I've used the EG import wizard for the sample data you've posted and added a few more test cases.

To exclude Saturday & Sunday from the calculation I've used interval WEEKDAY17W

Have a look below if the code returns the desired result.

DATA WORK.Sample_Data;
  LENGTH
    Src_Sys_Metering_Point_Num   8
    MPXN1              8
    SAP_Installation_Num   8
    Meter_Reading_Doc_Id   8
    ReadDate           8
    Meter_Read         8
    Meter_Reading_Type_Cd $ 2
    Meter_Read_Rsn     8
    CreatedDate        8
    COR_flag           8
    Date_Diff          8;
  FORMAT
    Src_Sys_Metering_Point_Num BEST12.
    MPXN1            BEST12.
    SAP_Installation_Num BEST12.
    Meter_Reading_Doc_Id BEST12.
    ReadDate         DATE9.
    Meter_Read       BEST12.
    Meter_Reading_Type_Cd $CHAR2.
    Meter_Read_Rsn   BEST12.
    CreatedDate      DATE9.
    COR_flag         BEST12.
    Date_Diff        BEST12.;
  INFORMAT
    Src_Sys_Metering_Point_Num BEST12.
    MPXN1            BEST12.
    SAP_Installation_Num BEST12.
    Meter_Reading_Doc_Id BEST12.
    ReadDate         DATE9.
    Meter_Read       BEST12.
    Meter_Reading_Type_Cd $CHAR2.
    Meter_Read_Rsn   BEST12.
    CreatedDate      DATE9.
    COR_flag         BEST12.
    Date_Diff        BEST12.;
  INFILE DATALINES4
    DLM='7F'x
    MISSOVER
    DSD;
  INPUT
    Src_Sys_Metering_Point_Num : BEST32.
    MPXN1            : BEST32.
    SAP_Installation_Num : BEST32.
    Meter_Reading_Doc_Id : BEST32.
    ReadDate         : BEST32.
    Meter_Read       : BEST32.
    Meter_Reading_Type_Cd : $CHAR2.
    Meter_Read_Rsn   : BEST32.
    CreatedDate      : BEST32.
    COR_flag         : BEST32.
    Date_Diff        : BEST32.;
  DATALINES4;
14262142626000373398552183654910CO12183610
14262142626000373759532180754910CO12180710
1426214262600037374272177654910CO12177610
14262142626000373781002174554910CO12174510
124331243360020314073321823746229I621840017
12433124336002031305042182201212182200
87757887757860020177605921822393301121839017
87757887757860020175853121822843861121839017
8775788775786002017845692182226480312182604
56276356276362333346061218380R92183901
5627635627636233337438921837769603R92183801
56276356276362333333734218370R92183801
562763562763623333334522183795563R92183801
5627635627636233333865021836749403R92183701
562763562763623333707102183670934R92183701
;;;;

/* add more test cases */
data WORK.Sample_Data;
  set WORK.Sample_Data(keep=ReadDate CreatedDate) end=last;
  output;

  /* add more test cases */
  if last then
    do;
      ReadDate='01Nov2019'd;
      CreatedDate='01Nov2019'd;
      do j=0 to 6;
        ReadDate=ReadDate+j;
        do i=0 to 10;
          CreatedDate=ReadDate+i;
          output;
        end;
      end;
    end;
run;

data want;
  set WORK.Sample_Data;
  format ReadDate CreatedDate weekdate.;
  format WorkDays_Diff 32.;
  format ReadDate_MonToFri CreatedDate_MonToFri weekdate.;
  ReadDate_MonToFri=intnx('WEEKDAY17W',ReadDate,0);
  CreatedDate_MonToFri=intnx('WEEKDAY17W',CreatedDate,0);
  WorkDays_Diff=intck ('Weekday',ReadDate, CreatedDate);
run;

proc print;
run;