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

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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