10-19-2016 09:22 AM
Need to know the syntax for using datapart with avg?
Here is what I have:
(AVG(Sales.Secured_Dt - Sales.Assigned_Dt)) AS Assigned_to_Secured
Now what is happening, when the query runs it is giving me "scientific notation".
Not sure how to incorporate the datepart to only look at the date and ignore the time.
10-19-2016 09:58 AM - edited 10-19-2016 10:12 AM
Neither of the input data exist in the temporary work library. Work is a temporary area SAS creates and uses by default to store data. The Error is telling those datasets do not appear there, though you have specified to take data from them:
FROM WORK.RDW1 Auction_Details INNER JOIN WORK.RDW2
You should have some connection to access - be it a libname statement or something similar, this should tell you where the data is going to in SAS. Then you would tell you from and join above to point at those libraries rather than work. There is nothing in the code you provide below which shows anything to do with an external source, only internal SAS.
I have also taken a moment to format the code so that I can read it and notice there are some typos in it:
proc sql; create table WANT as select SALES.SALE_DATE, SALES.PROGRAM, SALES.AUC_NAME, SALES.VIN, avg(datepart(SALES.SECURED_DT) - datepart(SALES.ASSIGNED_DT)) as ASSIGNED_TO_SECURED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.SECURED_DT)) as SECURED_TO_SOLD, avg(datepart(SALES.DAYS_TO_DELIVERY)) as DAYS_TO_DELIVERY, avg(datepart(SALES.ON_BLOCK_DT) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_ON_BLOCK, avg(datepart(SALES.SALE_DATE) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_SOLD, avg(datepart(SALES.TITLE_RCVD_DT) - datepart(SALES.SECURED_DT)) as SECURED_TO_TITLE_RECEIVED, avg(datepart(SALES.ASSIGNED_DT) - datepart(SALES.GROUNDING_DT)) as GROUNDED_TO_ASSIGNED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.LEGAL_SALE_DT)) as LEGAL_SALE_DATE_TO_SOLD, SALES.DAYS_ON_HOLD, case when SALES.PROGRAM ne "Lease" then SALES.SECURED_DT - SALES.REPO_DT else 0 end as REPO_TO_SECURED, case when SALES.PROGRAM in ('','Regular Dealer','Insurance Recovery','Manufacturer/Factory') then "Repo" else '' end as SOMETHING 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;
Note on the second case statment I have changed your logic to in(<list of strings>), the way you had it with the <variable>=<string> or <string> will not work. There are also uneven brackets, and function calls to datepart not closed, and no function call on the second date.
You could also, as your not really using the other dataset, drop the join completely and just check for existence (not sure if this will be more resource friendly, just a suggestion):
proc sql; create table WANT as select SALES.SALE_DATE, SALES.PROGRAM, SALES.AUC_NAME, SALES.VIN, avg(datepart(SALES.SECURED_DT) - datepart(SALES.ASSIGNED_DT)) as ASSIGNED_TO_SECURED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.SECURED_DT)) as SECURED_TO_SOLD, avg(datepart(SALES.DAYS_TO_DELIVERY)) as DAYS_TO_DELIVERY, avg(datepart(SALES.ON_BLOCK_DT) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_ON_BLOCK, avg(datepart(SALES.SALE_DATE) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_SOLD, avg(datepart(SALES.TITLE_RCVD_DT) - datepart(SALES.SECURED_DT)) as SECURED_TO_TITLE_RECEIVED, avg(datepart(SALES.ASSIGNED_DT) - datepart(SALES.GROUNDING_DT)) as GROUNDED_TO_ASSIGNED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.LEGAL_SALE_DT)) as LEGAL_SALE_DATE_TO_SOLD, SALES.DAYS_ON_HOLD, case when SALES.PROGRAM ne "Lease" then SALES.SECURED_DT - SALES.REPO_DT else 0 end as REPO_TO_SECURED, case when SALES.PROGRAM in ('','Regular Dealer','Insurance Recovery','Manufacturer/Factory') then "Repo" else '' end as SOMETHING from WORK.RDW2 SALES where SALES.CHANNEL='DOWNSTREAM' and exists(select distinct ACTIVE_FLAG from WORK.RDW1 where AUC_NAME=SALES.AUC_NAME and ACTIVE_FLAG='Y') and SALES.COMPANY='GMF' group by SALES.SALE_DATE, SALES.PROGRAM, SALES.AUC_NAME, SALES.VIN, SALES.DAYS_ON_HOLD; quit;
10-19-2016 09:30 AM - edited 10-19-2016 09:31 AM
You put the datetime variable inside brackets with the function call around it, as with any function:
(avg(datepart(sales.secured_dt) - datepart(sales.assigned_dt))) as assigned_to_secured
10-19-2016 09:33 AM
Just use the datepart function:
(AVG(datepart(Sales.Secured_Dt) - datepart(Sales.Assigned_Dt))) AS Assigned_to_Secured
This will cause problems if you are actually working with a DBMS and implicit or explicit SQL pass-through.
If that is the case, consider (depending on table size) pulling the data into SAS and do it there.
10-19-2016 09:50 AM
This is an access DB that I am pulling in tables from to create the query. While I have the two tables showing and pulling the data, i am now getting a:
ERROR: File WORK.RDW1.DATA does not exist.
ERROR: File WORK.RDW2.DATA does not exist.
I am not profecient enough yet to understand the errors.
Here is the query as a whole:
PROC SQL;
SELECT Sales.Sale_Date, Sales.Program, Sales.Auc_Name, Sales.VIN,
(AVG(DATEPART(Sales.Secured_Dt - Sales.Assigned_Dt))) AS Assigned_to_Secured,
(AVG(DATEPART(Sales.Sale_Date - Sales.Secured_Dt))) AS Secured_to_Sold,
(AVG(DATEPART(Sales.Days_To_Delivery))) AS Days_To_Delivery,
(AVG(DATEPART(Sales.On_Block_Dt - Sales.Title_Rcvd_Dt))) AS Title_Received_to_On_Block,
(AVG(DATEPART(Sales.Sale_Date - Sales.Title_Rcvd_Dt))) AS Title_Received_to_Sold,
(AVG(DATEPART(Sales.Title_Rcvd_Dt - Sales.Secured_Dt))) AS Secured_to_Title_Received,
(AVG(DATEPART(Sales.Assigned_Dt - Sales.Grounding_Dt))) AS Grounded_to_Assigned,
(AVG(DATEPART(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;
Why am I getting this now? I did put the datepart in as you suggested, then ran into this...
10-19-2016 09:58 AM - edited 10-19-2016 10:12 AM
Neither of the input data exist in the temporary work library. Work is a temporary area SAS creates and uses by default to store data. The Error is telling those datasets do not appear there, though you have specified to take data from them:
FROM WORK.RDW1 Auction_Details INNER JOIN WORK.RDW2
You should have some connection to access - be it a libname statement or something similar, this should tell you where the data is going to in SAS. Then you would tell you from and join above to point at those libraries rather than work. There is nothing in the code you provide below which shows anything to do with an external source, only internal SAS.
I have also taken a moment to format the code so that I can read it and notice there are some typos in it:
proc sql; create table WANT as select SALES.SALE_DATE, SALES.PROGRAM, SALES.AUC_NAME, SALES.VIN, avg(datepart(SALES.SECURED_DT) - datepart(SALES.ASSIGNED_DT)) as ASSIGNED_TO_SECURED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.SECURED_DT)) as SECURED_TO_SOLD, avg(datepart(SALES.DAYS_TO_DELIVERY)) as DAYS_TO_DELIVERY, avg(datepart(SALES.ON_BLOCK_DT) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_ON_BLOCK, avg(datepart(SALES.SALE_DATE) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_SOLD, avg(datepart(SALES.TITLE_RCVD_DT) - datepart(SALES.SECURED_DT)) as SECURED_TO_TITLE_RECEIVED, avg(datepart(SALES.ASSIGNED_DT) - datepart(SALES.GROUNDING_DT)) as GROUNDED_TO_ASSIGNED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.LEGAL_SALE_DT)) as LEGAL_SALE_DATE_TO_SOLD, SALES.DAYS_ON_HOLD, case when SALES.PROGRAM ne "Lease" then SALES.SECURED_DT - SALES.REPO_DT else 0 end as REPO_TO_SECURED, case when SALES.PROGRAM in ('','Regular Dealer','Insurance Recovery','Manufacturer/Factory') then "Repo" else '' end as SOMETHING 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;
Note on the second case statment I have changed your logic to in(<list of strings>), the way you had it with the <variable>=<string> or <string> will not work. There are also uneven brackets, and function calls to datepart not closed, and no function call on the second date.
You could also, as your not really using the other dataset, drop the join completely and just check for existence (not sure if this will be more resource friendly, just a suggestion):
proc sql; create table WANT as select SALES.SALE_DATE, SALES.PROGRAM, SALES.AUC_NAME, SALES.VIN, avg(datepart(SALES.SECURED_DT) - datepart(SALES.ASSIGNED_DT)) as ASSIGNED_TO_SECURED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.SECURED_DT)) as SECURED_TO_SOLD, avg(datepart(SALES.DAYS_TO_DELIVERY)) as DAYS_TO_DELIVERY, avg(datepart(SALES.ON_BLOCK_DT) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_ON_BLOCK, avg(datepart(SALES.SALE_DATE) - datepart(SALES.TITLE_RCVD_DT)) as TITLE_RECEIVED_TO_SOLD, avg(datepart(SALES.TITLE_RCVD_DT) - datepart(SALES.SECURED_DT)) as SECURED_TO_TITLE_RECEIVED, avg(datepart(SALES.ASSIGNED_DT) - datepart(SALES.GROUNDING_DT)) as GROUNDED_TO_ASSIGNED, avg(datepart(SALES.SALE_DATE) - datepart(SALES.LEGAL_SALE_DT)) as LEGAL_SALE_DATE_TO_SOLD, SALES.DAYS_ON_HOLD, case when SALES.PROGRAM ne "Lease" then SALES.SECURED_DT - SALES.REPO_DT else 0 end as REPO_TO_SECURED, case when SALES.PROGRAM in ('','Regular Dealer','Insurance Recovery','Manufacturer/Factory') then "Repo" else '' end as SOMETHING from WORK.RDW2 SALES where SALES.CHANNEL='DOWNSTREAM' and exists(select distinct ACTIVE_FLAG from WORK.RDW1 where AUC_NAME=SALES.AUC_NAME and ACTIVE_FLAG='Y') and SALES.COMPANY='GMF' group by SALES.SALE_DATE, SALES.PROGRAM, SALES.AUC_NAME, SALES.VIN, SALES.DAYS_ON_HOLD; quit;
10-19-2016 11:17 AM
Thank you for the cleaner version of SAS query. It still does not work. Still getting:
ERROR: File WORK.RDW1.DATA does not exist.
ERROR: File WORK.RDW2.DATA does not exist.
I am going to go through the tutorial again to figure out my issue.
Regards,
TM
Need further help from the community? Please ask a new question.