BookmarkSubscribeRSS Feed
TMiller16
Fluorite | Level 6

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

 

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,

_____

79

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

22

76

41 ! Cognos,

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

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

 

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

TMiller16
Fluorite | Level 6
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

TMiller16
Fluorite | Level 6
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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

S.Dlr_GMF_ID AS 'Sold Dealer Code Cognos',

 

To

 

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#p18cdcs4v5wd2dn1q0x2...

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

dntran16
Calcite | Level 5
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 3173 views
  • 0 likes
  • 4 in conversation