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?
