topic Re: Difference between days in SAS Programming

Difference between days

RandyStan — Sat, 10 Jul 2021 15:47:09 GMT

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

Re: Difference between days

ballardw — Sat, 10 Jul 2021 17:21:13 GMT

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/ta-p/424354 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.

Re: Difference between days

PaigeMiller — Sat, 10 Jul 2021 17:52:30 GMT

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.

Re: Difference between days

RandyStan — Sun, 11 Jul 2021 14:05:15 GMT

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

Re: Difference between days

PaigeMiller — Sun, 11 Jul 2021 14:14:52 GMT

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.

Re: Difference between days

RandyStan — Sun, 11 Jul 2021 15:37:21 GMT

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

Re: Difference between days

PaigeMiller — Sun, 11 Jul 2021 18:35:33 GMT

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.

Re: Difference between days

ChrisNZ — Mon, 12 Jul 2021 07:56:40 GMT

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

Re: Difference between days

Patrick — Mon, 12 Jul 2021 09:09:45 GMT

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

Re: Difference between days

ballardw — Mon, 12 Jul 2021 15:01:35 GMT

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