topic Re: Proc sql in SAS Programming

Proc sql

Prateek1 — Tue, 26 Jul 2016 09:53:10 GMT

hi,

proc sql;
create table NZ AS
Select Olympicscs1.Probability,Olympicscs1.Account_Name as Client,Olympicscs1.Champ,Olympicscs1.Last_Modified_Date as Modified,
format MMDDYY8.,Olympicscs1.Deal_Comments,Olympicscs1.Tot_Budget format dollar20., Olympicscs2.Digi_Budget format dollar20.
FROM Olympicscs1,Olympicscs2
Where Olympicscs1.Probability=Olympicscs2.Prob_Digi and
Olympicscs1.Account_Name=Olympicscs2.Account_Name and Olympicscs1.Probability NE 0
ORDER BY Olympicscs1.Probability;
quit;

error:

Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <,
<=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT,
INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

what should be the code ?

Re: Proc sql

error_prone — Tue, 26 Jul 2016 12:19:50 GMT

Maybe the comma after "Olympicscs1.Last_Modified_Date as Modified" is the culprit.

Re: Proc sql

toconero — Tue, 26 Jul 2016 12:26:35 GMT

Hi Prateek1

You should drop , after Olympicscs1.Last_Modified_Date as Modified in this way format MMDDYY8. runs for this variable, on other hand you should write a variable for the format.

proc sql;
create table NZ AS
Select Olympicscs1.Probability,Olympicscs1.Account_Name as Client,Olympicscs1.Champ,Olympicscs1.Last_Modified_Date as Modified format MMDDYY8.,Olympicscs1.Deal_Comments,Olympicscs1.Tot_Budget format dollar20., Olympicscs2.Digi_Budget format dollar20.
FROM Olympicscs1,Olympicscs2
Where Olympicscs1.Probability=Olympicscs2.Prob_Digi and
Olympicscs1.Account_Name=Olympicscs2.Account_Name and Olympicscs1.Probability NE 0
ORDER BY Olympicscs1.Probability;
quit;

Re: Proc sql

Kurt_Bremser — Tue, 26 Jul 2016 13:13:28 GMT

Simple code formatting lets the error stand out like a beacon:

proc sql;
create table NZ as select
  Olympicscs1.Probability,
  Olympicscs1.Account_Name as Client,
  Olympicscs1.Champ,
  Olympicscs1.Last_Modified_Date as Modified,
  format MMDDYY8.,
  Olympicscs1.Deal_Comments,
  Olympicscs1.Tot_Budget format dollar20.,
  Olympicscs2.Digi_Budget format dollar20.
from
  Olympicscs1,
  Olympicscs2
where
  Olympicscs1.Probability=Olympicscs2.Prob_Digi and
  Olympicscs1.Account_Name=Olympicscs2.Account_Name and
  Olympicscs1.Probability NE 0
order by Olympicscs1.Probability
;
quit;

Writing spaghetti code is the main culprit here.