topic Re: Error Messages in SAS Programming

Error Messages

TMiller16 — Fri, 18 Nov 2016 16:18:29 GMT

I have tried a few things to no eval, please help fix....ugh

37 PROC SQL NOEXEC;

39 SELECT BD.BIDDER_ORGANIZATION_NAME, BD.VIN, BD.YEAR, BD.MAKE, BD.MODEL, BD.MILEAGE, BD.BUY_PRICE, BD.BID_AMOUNT,

39 ! S.Sale_Price AS Purchase Price Cognos,

_____

ERROR 79-322: Expecting a FROM.

40 BD.BID_DATE, BD.BID_SITE, BD.BIDDER_DEALER_CODE, BD.BIDDER_STATE, BD.BIDDER_ZIP, BD.INTERATION_SOLD,

40 ! BD.SOLD_WEBSITE, BD.SALE_DATE,

41 YEAR(BD.BID_DATE) AS BID YEAR, MONTH(BD.BID_DATE) AS BID MONTH, S.Company, S.Dlr_GMF_ID AS Sold Dealer Code

____

2 The SAS System 08:29 Friday, November 18, 2016

41 ! Cognos,

ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, ORDER.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Error Messages

RW9 — Fri, 18 Nov 2016 16:24:52 GMT

Can you post the original code as is, there appear to be various parts missing in that snippet - create table or view, from etc.

Re: Error Messages

TMiller16 — Fri, 18 Nov 2016 16:28:31 GMT

37 PROC SQL NOEXEC;

38
39 SELECT BD.BIDDER_ORGANIZATION_NAME, BD.VIN, BD.YEAR, BD.MAKE, BD.MODEL, BD.MILEAGE, BD.BUY_PRICE, BD.BID_AMOUNT,
39 ! S.Sale_Price AS 'Purchase Price Cognos',
_______________________
22
ERROR 22-322: Expecting a name.

40 BD.BID_DATE, BD.BID_SITE, BD.BIDDER_DEALER_CODE, BD.BIDDER_STATE, BD.BIDDER_ZIP, BD.INTERATION_SOLD,
40 ! BD.SOLD_WEBSITE, BD.SALE_DATE,
41 YEAR(BD.BID_DATE) AS 'BID YEAR', MONTH(BD.BID_DATE) AS 'BID MONTH', S.Company, S.Dlr_GMF_ID AS 'Sold Dealer
__________ ___________
2 The SAS System 08:29 Friday, November 18, 2016

_________________________
22 22 22
41 ! Code Cognos',
ERROR 22-322: Expecting a name.

42
43 (CASE WHEN BD.BIDDER_DEALER_CODE = S.Dlr_GMF_ID
44 THEN 'WON'
45 ELSE 'LOST' END) AS 'WON LOSS',
__________
22
ERROR 22-322: Expecting a name.

46
47 FROM BidData DB
_______
79
ERROR 79-322: Expecting a FROM.

48 INNER JOIN Sales S ON (DB.VIN = S.VIN)
49 AND (BD.BIDDER_DEALER_CODE = S.Dlr_GMF_ID),
50 INNER JOIN Active_Dealers AD ON (S.Dlr.GMF.ID = AD.Dlr.GMF.ID)
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

51
52 WHERE AD.RSS_Flag = '1'
53
54 GROUP BY BD.VIN;
55
56 RUN;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
57
58 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Error Messages

RW9 — Fri, 18 Nov 2016 16:36:38 GMT

No, I meant post the original code, not the log. However I do note that at a couple of places you have:

S.Sale_Price AS 'Purchase Price Cognos',

In SAS you don't do this, you may be able to put n after the text string and get it to work (not tested):

S.Sale_Price AS 'Purchase Price Cognos'n,

However I would really advise not to. If you doing this in SAS, use the terminology which is correct across ANSI SQL:
S.Sale_Price AS PURCHASE_PRICE_COGNOS,

Ie. a variable name.

Re: Error Messages

TMiller16 — Fri, 18 Nov 2016 16:39:37 GMT

Here you go, sorry misunderstood what you were looking for,

PROC IMPORT TABLE="Sales" OUT=SASUSER.Sales
DBMS=access
REPLACE;
DATABASE="\\americredit.com\db_files\AOC\SA\AR\Analytics\RDW.accdb";

PROC IMPORT TABLE="Active_Dealers" OUT=SASUSER.Active_Dealers
DBMS=access
REPLACE;
DATABASE="\\americredit.com\db_files\AOC\SA\AR\Analytics\RDW.accdb";

PROC IMPORT DATAFILE="X:\Analytics\Reports\Sources" OUT=SASUSER.BidData
DBMS=xlsx
REPLACE;
SHEET="BidData";
GETNAMES=yes;

PROC SQL NOEXEC;

SELECT BD.BIDDER_ORGANIZATION_NAME, BD.VIN, BD.YEAR, BD.MAKE, BD.MODEL, BD.MILEAGE, BD.BUY_PRICE, BD.BID_AMOUNT, S.Sale_Price AS 'Purchase Price Cognos',
BD.BID_DATE, BD.BID_SITE, BD.BIDDER_DEALER_CODE, BD.BIDDER_STATE, BD.BIDDER_ZIP, BD.INTERATION_SOLD, BD.SOLD_WEBSITE, BD.SALE_DATE,
YEAR(BD.BID_DATE) AS 'BID YEAR', MONTH(BD.BID_DATE) AS 'BID MONTH', S.Company, S.Dlr_GMF_ID AS 'Sold Dealer Code Cognos',

(CASE WHEN BD.BIDDER_DEALER_CODE = S.Dlr_GMF_ID
THEN 'WON'
ELSE 'LOST' END) AS 'WON LOSS',

FROM BidData DB
INNER JOIN Sales S ON (DB.VIN = S.VIN)
AND (BD.BIDDER_DEALER_CODE = S.Dlr_GMF_ID),
INNER JOIN Active_Dealers AD ON (S.Dlr.GMF.ID = AD.Dlr.GMF.ID)

WHERE AD.RSS_Flag = '1'

GROUP BY BD.VIN;

RUN;

QUIT;

Re: Error Messages

RW9 — Fri, 18 Nov 2016 16:51:14 GMT

I would definately start by changing those with the quoted text for variables:

S.Dlr_GMF_ID AS 'Sold Dealer Code Cognos',

S.Dlr_GMF_ID AS Sold_Dealer_Code_Cognos,

I would also advise some code formatting there (consitent casing, indentation, finisihing steps e.g with run;) as its quite difficult to read that for example - using the {i} above the post:

proc import table="sales" out=sasuser.sales
  dbms=access
  replace;
  database="\\americredit.com\db_files\aoc\sa\ar\analytics\rdw.accdb";
run;

To add, what you have there is Named Literals - but you are missing the "n" to indicate that they are so. You will find information on them here:

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#p18cdcs4v5wd2dn1q0x296d3qek6.htm

I would still suggest avoiding the use of them however, there doesn't seem to be any need in the code you give.

Re: Error Messages

Kurt_Bremser — Fri, 18 Nov 2016 17:12:21 GMT

Just use valid variable names, and your problems will be gone.

'WIN LOSS' is not a valid SAS name, for instance.

Re: Error Messages

dntran16 — Fri, 18 Nov 2016 17:27:12 GMT

You can use label:
Eg: S.Sale_Price AS 'Purchase Price Cognos'
to S.Sale_Price AS P_Price_Cognos label = 'Purchase Price Cognos'
etc