sas function that works with date variables

[noetsi]

I know SAS functions that get the difference between two dates. But I can't find a function that works with date variables as compared to values.

for instance datdif(('01aug2018'd,'02aug2018'd,"actual") generates fine results

but if you do datdif(datevariable1,datevariable2,"actual") I get all missing values.

I am using variable SAS knows are dates so I don't understand the issue. INTCK does not work either

 

[Dason]

I am using variable SAS knows are dates so I don't understand the issue. INTCK does not work either

I don't actually trust you when you guarantee that those are dates.

 

[hlsmith]

So your question is how to get difference between dates? I just go diff = var1 - var2;

Right?

 

[Con-Tester]

It must be clearly understood that SAS has just two data types, namely text and numeric. The numeric data type is in all cases the IEEE 754 double-precision floating-point type (8-byte real). SAS doesn’t even distinguish between integer and floating-point numeric types; all numeric types are stored in the same way. It’s the SAS format that’s associated with a particular numeric data column that determines how SAS shows the data to the user, e.g., date, currency, time, representation (scientific, engineering, general), how many decimal places, etc. It is important to understand this aspect of SAS because it often clears up basic misunderstandings.

What the above means is that there’s no requirement that a date value must have a date format before it can be passed to a date function. Or, for that matter, that any type-specific numeric calculation requires that the arguments have the correct format before the function will process them. All that’s needed is that the underlying values of the numeric variables are within the allowable range. That is to say, the associated format is irrelevant and is merely a convenience for humans viewing the data.

All of that said, I suspect the reason NULL values are returned by “datdif(datevariable1,datevariable2,"actual")” is that either (1) “datevariable1” or “datevariable2” is NULL, possibly both, and/or (2) that one or both of them are outside the allowable range. Reason (2) could happen if the variables are “datetime” types rather than “date” types (the value mappings are very different between these two types), and the correct date value can be extracted from a datetime variable using the SAS function “datepart(DateTimeVar)”.

If you know for a fact that the two variables are proper non-NULL SAS dates then hlsmith’s method will give the number of days between “datevariable1” and “datevariable2” because SAS’s date representation counts the integer number of days from a base date (01 January 1960). See more here.

 

[noetsi]

I found code to do it. The trick is the fields I was using were date time not date fields and that was causing problems (as noted above which I had not seen). You can not subtract a date time field from a date time field. You have to remove the date first with a datepart.

This works (although I am not sure if it works with leap years)

Proc Sql;
Create Table work.test as
Select
vrid
,db12
,refdate
,(datepart(db12)) - (datepart (refdate)) as dd
from work.area1
order by 3;
run;
quit;

 

[Con-Tester]

This works (although I am not sure if it works with leap years)

Yes, it will work correctly for all valid dates, including leap years, because, as mentioned previously, SAS stores dates as the integer number of days since 01 January 1960, where a negative value is a date prior to that reference date. (However, note that there are upper and lower limits to the range of dates that SAS recognises as valid.) Thus, date calculations then come down to simple arithmetic, whereas it’s the formatting—i.e., turning those integers back into recognisable dates—that is the trickier part. Also, it would obviously be all sorts of problematic if SAS didn’t take leap years into account.

But you can test the leap year thing yourself in SAS:

data Test;
format Date date9.;
Date = "28FEB2016"D; /* This is a leap year */
DateVal = 1*Date;
output;
Date = "29FEB2016"D;
DateVal = 1*Date;
output;
Date = "01MAR2016"D;
DateVal = 1*Date;
output;
run;

Run the above code and then open the Work.Test dataset. The "Date" column will show three consecutive dates, while the "DateVal" column will show the underlying integer value corresponding to each date. These too will be consecutive.

 

[noetsi]

thanks.