General SAS Programming

[TMiller16]

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.

[RW9 · Accepted solution]

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;

[RW9]

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

[KurtBremser]

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.

[TMiller16]

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

[RW9 · Accepted solution]

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;

[TMiller16]

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