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?
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.
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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.