ERROR: File DBO.prop_FinalEstimates.DATA does not exist.

nicole_e_allen — Fri, 30 Apr 2021 18:40:31 GMT

I am working on a project at work and using the following code:

libname CP odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claims-claimsproperty-01 ;

DATABASE=ClaimsProperty; TRUSTED_CONNECTION=YES" schema=PD;

LIBNAME PD ODBC NOPROMPT="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claims-claimsproperty-01 ;

DATABASE=ClaimsProperty; TRUSTED_CONNECTION=YES" SCHEMA=PD;

libname DBO odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claimscontrolbi-01 ;

DATABASE=BISSClaimsCntrlRpt; TRUSTED_CONNECTION=YES" schema=dbo;

libname DBO odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claimscontrolbi-01 ;

DATABASE=BISSClaimsCmnDmnsn; TRUSTED_CONNECTION=YES" schema=dbo;

PROC SQL;

CREATE TABLE Estimates AS

SELECT E.ClaimYear,

E.ClaimNbr,

E.PropNbr,

E.PropId,

PL.FinalEstimateAcctMonth,

E.FinalEstimateVehicleModelYear,

E.FinalEstimateVehicleMakeDesc,

E.FinalEstimateVehicleModelDesc,

F.FacilityId,

F.FacilityStateCode,

F.SWEShopInd

FROM PD.Estimate as E

LEFT JOIN PD.Date as D

ON E.FinalEstimateCompleteDateId = D.DateId

LEFT JOIN PD.Property as P

ON E.ClaimYear = P.ClaimYear

AND E.ClaimNbr = P.ClaimNbr

AND E.PropNbr = P.PropNbr

LEFT JOIN PD.PropertyPlus as PP

ON E.ClaimYear = PP.ClaimYear

AND E.ClaimNbr = PP.ClaimNbr

LEFT JOIN PD.Inspection as Ins

ON E.PropId = Ins.PropId

LEFT JOIN PD.Facility as F

ON Ins.FacilityId = F.FacilityId

LEFT JOIN PD.PartsLaborShopSummary as PL

ON E.PropId=PL.PropId

LEFT JOIN PD.Repair as R

ON E.PropId=R.PropId

WHERE PL.FinalEstimateAcctMonth > 202003 and PL.FinalEstimateAcctMonth < 202102

AND P.TotalLossInd = 0

AND P.PropTypeDesc = 'Vehicle'

AND E.TotalEstimateLineCnt >= 50

AND F.FacilityStateCode in ('IA','MI','UT')

AND F.SWEShopInd = 1;

%LET INFILE = \\prog1\east\AppsProd\neclaims\Nicole Allen\RFP Project\RFP Project Shops.xlsx;

PROC IMPORT OUT= ExcelFacilityName

DATAFILE= "&INFILE."

DBMS= XLSX REPLACE;

GETNAMES= Yes;

RUN;

PROC SQL;

CREATE TABLE RepairFacilityNames AS

SELECT A.*, B.*

From Estimates AS A

LEFT JOIN ExcelFacilityName AS B

ON B.FacilityId = A.FacilityId;

QUIT;

/** Repair Time Query **/

PROC SQL;

CREATE TABLE RepairTime AS

Select DISTINCT C.*,

sum(FE.RepairLaborHrs) as EstRepairTime

from RepairFacilityNames AS C

left join DBO.PROP_FinalEstimates as FE

ON A.PropId = FE.PropID

where FE.ReportingAcctMonth >= 202003 and FE.ReportingAcctMonth <= 202102

group by FE.EstState,FE.RepairFacilityName,FE.claimrefnbr

order by FE.EstState,FE.RepairFacilityName,FE.claimrefnbr;

QUIT;

The very last PROC SQL statement I keep getting the error: ERROR: File DBO.prop_FinalEstimates.DATA does not exist

I get no other errors in my log. Thoughts?

Re: ERROR: File DBO.prop_FinalEstimates.DATA does not exist.

ballardw — Fri, 30 Apr 2021 19:11:59 GMT

Check the spelling and look in your DBO library.

But since you defined TWO Libname DBO statements

libname DBO odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claimscontrolbi-01 ;

DATABASE=BISSClaimsCntrlRpt; TRUSTED_CONNECTION=YES" schema=dbo;

libname DBO odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claimscontrolbi-01 ;

DATABASE=BISSClaimsCmnDmnsn; TRUSTED_CONNECTION=YES" schema=dbo;

I suspect that the data set you expect is in the BISSClainsCtrlRpt and not in the BISSClaimsCmnDmnsn. Your second libname DBO is the one in effect.

Re: ERROR: File DBO.prop_FinalEstimates.DATA does not exist.

nicole_e_allen — Fri, 30 Apr 2021 19:31:49 GMT

You were right! One of the DBO libnames I didnt actually need in the code (it was erronously brought over from another code), and it was looking for that table in the wrong database. Thank you!

topic Re: ERROR: File DBO.prop_FinalEstimates.DATA does not exist. in SAS Procedures

ERROR: File DBO.prop_FinalEstimates.DATA does not exist.

Re: ERROR: File DBO.prop_FinalEstimates.DATA does not exist.

Re: ERROR: File DBO.prop_FinalEstimates.DATA does not exist.