Calculating days using dates from Access

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Calculating days using dates from Access

I have two tables that I brought in from Access.  in the tables there are date columns that are date/time.  When I build the query and run it I get some that come back with the value and some that come back with lack of better term "scientific notation".  Each colum is the same but in SAS it does not calculate correctly.

 

When I run it in Access I get the desired result.

 

Here is the actual query:

 

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_RDW1 AS

SELECT Sales.Sale_Date, Sales.Program, Sales.Auc_Name, Sales.VIN,

(AVG(Sales.Secured_Dt - Sales.Assigned_Dt)) AS Assigned_to_Secured,

(AVG(Sales.Sale_Date - Sales.Secured_Dt)) AS Secured_to_Sold,

(AVG(Sales.Days_To_Delivery)) AS Days_To_Delivery,

(AVG(Sales.On_Block_Dt - Sales.Title_Rcvd_Dt)) AS Title_Received_to_On_Block,

(AVG(Sales.Sale_Date - Sales.Title_Rcvd_Dt)) AS Title_Received_to_Sold,

(AVG(Sales.Title_Rcvd_Dt - Sales.Secured_Dt)) AS Secured_to_Title_Received,

(AVG(Sales.Assigned_Dt - Sales.Grounding_Dt)) AS Grounded_to_Assigned,

(AVG(Sales.Sale_Date - Sales.Legal_Sale_Dt)) AS Legal_Sale_Date_to_Sold,

Sales.Days_On_Hold,

CASE WHEN (Sales.Program <> "Lease") THEN (Sales.Secured_Dt - Sales.Repo_Dt) ELSE 0 END AS Repo_to_Secured,

CASE WHEN (Sales.Program = '' OR 'Regular Dealer' OR 'Insurance Recovery' OR 'Manufacturer/Factory') THEN "Repo" ELSE '' END

FROM WORK.RDW1 Auction_Details

INNER JOIN WORK.RDW2 Sales ON (Auction_Details.Auc_Name = Sales.Auc_Name)

WHERE Sales.Channel = 'Downstream'

AND Auction_Details.Active_Flag = 'Y'

AND Sales.Company = 'GMF'

GROUP BY Sales.Sale_Date, Sales.Program, Sales.Auc_Name, Sales.VIN, Sales.Days_On_Hold;

QUIT;

 

I know there will be more quesitons so fire away.

 

 


Accepted Solutions
Solution
‎10-18-2016 03:59 PM
Super User
Posts: 5,260

Re: Calculating days using dates from Access

In SAS there's a difference between dates and datetimes and they are unfortunately not casted automatically when combined.
If your Avg is in days you can convert datetime to date using the datepart() function.
Data never sleeps

View solution in original post


All Replies
Solution
‎10-18-2016 03:59 PM
Super User
Posts: 5,260

Re: Calculating days using dates from Access

In SAS there's a difference between dates and datetimes and they are unfortunately not casted automatically when combined.
If your Avg is in days you can convert datetime to date using the datepart() function.
Data never sleeps
Contributor
Posts: 35

Re: Calculating days using dates from Access

TY, I will give this a shot!  Greatly appreciate the feedback!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 213 views
  • 1 like
  • 2 in conversation