Difference between days

[RandyStan]

Dear All:

My data is as follows:

ID        Date

1         01JUL2021

1         02JUL2021

1         06JUL2021

1         09JUL2021

2         02MAR2021

2         09MAR2021

2         12MAR2021

3         14MAY2021

I want Work Days Between Trades and my data should be as follows.  The first observation of each ID should be -50000

ID        Date                        Work_Days_Between_Visits

1         01JUL2021                -50000

1         02JUL2021                  0

1         06JUL2021                  0  /* 5 July was a Holiday*/

1         09JUL2021                  2    

2         02MAR2021              -50000         

2         09MAR2021               4

2         12MAR2021               2

3         14MAY2021             -50000

Please Help.  And Thanks in Advance

[ballardw]

Why is 2 July for ID 1 zero?

And if the idea is "days between trades" why the heck is 6 July not 4 (from 2 July). How do you get 4 for the value between 9 Mar and 2 Mar?

Your rules are pretty incomplete. Stating a value for one case does not explain the general rule that would be needed for coding.

Is you "date" an actual SAS date value or a character value? There are functions that will return information like is a specific date a known holiday or a week day but they won't work without a SAS date valued variable.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

And can you explain why -50000 is a desirable value. For almost any work in SAS it would be preferable to use a missing value instead of some obscure number.

The LAG function can get the value for a variable on a previous record. If the data is sorted by ID and date then you can identify the first record for an ID with the SAS automatic variable First.Id if you process data with a BY statement.

If you can provide data in the form of a working data step then you may get some example code for bits of this.

[PaigeMiller]

Hello, @RandyStan, I have a particular problem with this:

My data is as follows:

ID        Date
1         01JUL2021
1         02JUL2021
1         06JUL2021
1         09JUL2021
2         02MAR2021
2         09MAR2021
2         12MAR2021
3         14MAY2021

We usually ask for data in the form of a SAS data step, such as the output of these instructions. Why? Because we need to know if the variables are numeric or character, this is critical for dates, and we cannot be sure whatever code we provide will work without knowing this. Please, in this case and in the future, provide data according to the instructions.

[RandyStan]

Thank you.

I want the first value to be a NULL value.  So I randomly chose -50000.  

The date format is DATE9. SAS date format.

The file is attached..  In this file I left the first cell of each ID blank to indicate a NULL value.

   Thank you so much

[PaigeMiller]

Excel files don't help us understand if your SAS data set contains numeric dates or character dates. And many of us will not download attachments. So, again, I ask you to follow the instructions given (and @ballardw has also asked) and provide the data as a SAS data step.

The choice of -50000 (or any non-missing value) is a particularly poor choice, and it should be set to missing.

[RandyStan]

I hope this is what was wanted.

Thanks much

data WDBV;

input ID  date DATE9. ; 

cards ;

1              01JUL2021

1              02JUL2021

1              06JUL2021

1              09JUL2021

2              02MAR2021

2              09MAR2021

2              12MAR2021

3              14MAY2021

;

What I need is

ID            Date                Work_Days_Between_Visits

1              01JUL2021

1              02JUL2021              0 

1              06JUL2021              0

1              09JUL2021              2

2              02MAR2021          

2              09MAR2021             4

2              12MAR2021             2

3              14MAY2021       

[PaigeMiller]

Stealing shamelessly from @ChrisHemedinger 's article on computing work-days, here is some code:

data WDBV;
input ID date :DATE9. ; 
cards ;
1              01JUL2021
1              02JUL2021
1              06JUL2021
1              09JUL2021
2              02MAR2021
2              09MAR2021
2              12MAR2021
3              14MAY2021
;

proc fcmp  outlib=work.myfuncs.dates;
  function networkdays(d1,d2,holidayDataset $,dateColumn $);
 
    /* make sure the start date < end date */
    start_date = min(d1,d2);
    end_date = max(d1,d2);
 
    /* read holiday data into array */
    /* array will resize as necessary */
    array holidays[1] / nosymbols; 
    if (not missing(holidayDataset) and exist(holidayDataset)) then
        rc = read_array(holidayDataset, holidays, dateColumn);
    else put "NOTE: networkdays(): No Holiday data considered"; 
 
    /* INTCK computes transitions from one day to the next */
    /* To include the start date, if it is a weekday, then */
    /*  make the start date one day earlier.               */
    if (1 < weekday(start_date)< 7) then 
       calc_start_date = start_date-1; 
    else 
       calc_start_date = start_date;
    diff = intck('WEEKDAY', calc_start_date, end_date);
    do i = 1 to dim(holidays);
      if (1 < weekday(holidays[i])< 7) and
         (start_date <= holidays[i] <= end_date) then
            diff = diff - 1; 
    end; 
    return(diff);
  endsub; 
run; 
quit;

options cmplib=work.myfuncs;
data want;
    set wdbv;
    by id;
    prev_date=lag(date);
    if not first.id then do;
	   diff1 = intck('WEEKDAY', prev_date, date)-1; /* DIFF1 counts weekdays */
	   /* DIFF2 counts working days */
       diff2 = networkdays(prev_date, date, "work.usholidays","holidaydate")-2;
    end;
    format prev_date date date9.;
run;
	   

It's not clear to me why your output has 0 work days between 02JUL2021 and 06JUL2021, as it seems to me that there is one work day.

[Patrick]

@RandyStan 

Holidays are just a pleasure for such problems especially because the differ between states. Many organizations have somewhere a working day table (or a table with holidays). If so then you could use such a table to generate a custom interval and then use this custom interval with the intck() function to get what you want.

[ballardw]

---

@RandyStan wrote:

Thank you.

I want the first value to be a NULL value.  So I randomly chose -50000.  

The date format is DATE9. SAS date format.

The file is attached..  In this file I left the first cell of each ID blank to indicate a NULL value.

   Thank you so much

---

SAS has a missing value that is basically a "NULL". For numeric values the default displays as a dot in tables.

You can assign a missing value to a variable a couple of ways in a data step.

Variable = . ;

Call missing(variable);

are the easiest. Call missing will take a list of variables and works with character and numeric variables.

[ChrisNZ]

To get the starting -50000, test  first.ID  (so you must use a BY statement).

To count the days, do a loop from lag(DATE) to DATE, and test if each day is either a weekend  (function WEEKDAY) or a holiday (function HOLIDAY if you are in the US, which you seem to be).