Subtracting dates in Proc SQL/SAS-CodePudding

I have a Proc SQL code block running in SAS Enterprise Guide. I need to find the number of days between two dates

proc sql;
select col_A,col_B,col_C, (put (date(),yymmdd10.) - col_C) as age_bucket
from DB2.Table_A;
quit;

col_C is a date of the 'YYYY-MM-DD' format (e.g. 2022-05-02) I am subtracting col_C from today's date and want to get the total number of days between them in as age_bucket. I am getting the following error.

ERROR: Expression using subtraction (-) requires numeric types.

How do I go about this? The Table_A is from DB2 database.

CodePudding user response：

Using the PUT() function to convert the current date into a character string is going to make it impossible to perform arithmetic with the result.

If COL_C has a DATE value it does not matter how the value is displayed (formats just impact how values are displayed). A DATE value is just the number of days since 1960. You only need to subtract the two numbers to calculate the difference in days.

 (date() - col_C) as age_bucket

If COL_C has a DATETIME value (number of seconds since 1960) then first convert it to a DATE value.

 (date() - datepart(col_C)) as age_bucket

If COL_C is a character string in the style YYYY-MM-DD then use the INPUT() function with the YYMMDD informat to convert the string into a date value.

 (date() - input(col_C,yymmdd10.)) as age_bucket

CodePudding user response：

I think DB2 supports the DAYS_BETWEEN() function which will give you the number of days between the 2 date arguments. https://www.db2tutorial.com/db2-date-functions/

CodePudding user response：

Use YRDIF Function to get age in years. Subtracting dates will get your age in days.

Both dates should be SAS dates, numeric with a date format.

proc sql;
select col_A, 
       col_B, 
       col_C,  
       floor(YRDIF(input(col_C, yymmdd10.), today(),"AGE"))  as age_bucket
from DB2.Table_A;
quit;