BookmarkSubscribeRSS Feed
nicole_e_allen
Calcite | Level 5

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?

 

2 REPLIES 2
ballardw
Super User

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.

nicole_e_allen
Calcite | Level 5

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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 327 views
  • 0 likes
  • 2 in conversation