topic Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e in SAS Programming

ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax error

shweta_234 — Wed, 29 Apr 2020 12:34:06 GMT

my code is

proc sql;
create gvkeys as select distinct gvkey, lpermno as permno, tic, coaslesce(linkenddt,'31dec9999') as linkenddt format date9.,tic,
linkdt format date9. from Gvkeytable;
quit;

I am getting error

ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW.

ERROR 76-322: Syntax error

can someone tell me what the error is?

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

Shmuel — Wed, 29 Apr 2020 12:39:55 GMT

You miss the word TABLE after the CREATE and

why is tic selected twice?

proc sql;
create table gvkeys as 
  select distinct gvkey, lpermno as permno, 
  tic,
  coaslesce(linkenddt,'31dec9999') as linkenddt format date9., 
  tic,
  linkdt format date9. 
from Gvkeytable;
quit;

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

shweta_234 — Wed, 29 Apr 2020 12:42:00 GMT

hi i corrected that but still getting this error
ERROR: Function COASLESCE could not be located.
ERROR: Character expression requires a character format.

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

shweta_234 — Wed, 29 Apr 2020 12:45:07 GMT

the variable linkenddt has dates of format 30-Jun-78 and in some observations it is 'E".

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

Shmuel — Wed, 29 Apr 2020 13:10:40 GMT

the function is COALESCE

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

DavePrinsloo — Wed, 29 Apr 2020 13:12:24 GMT

'31dec9999' is a string. If you want it to be a date then add a d (for date) after the string, eg. use '31dec9999'd

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

DavePrinsloo — Wed, 29 Apr 2020 13:14:11 GMT

and you need to spell correctly! COASLESCE is actually COALESCE.
I suggest you check these simple errors before posting!

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

shweta_234 — Wed, 29 Apr 2020 13:16:32 GMT

the variable to which i want to use 31dec9999 has dates in some observations and 'E' in some observations.
How do i get rid of the E's?

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

Shmuel — Wed, 29 Apr 2020 13:19:51 GMT

It is not the format that matters but the variable type.

Is it a sas date, a numeric variable? then the date literal need be fixed:

proc sql;
create table gvkeys as 
  select distinct gvkey, lpermno as permno, 
  tic,
  coalesce(linkenddt, '31dec9999'd) as linkenddt format date9., 
  linkdt format date9. 
from Gvkeytable;
quit;

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

DavePrinsloo — Wed, 29 Apr 2020 13:22:03 GMT

Look up the syntax to use CASE in your SQL select

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

Shmuel — Wed, 29 Apr 2020 14:01:45 GMT

The date variable is either numeric with a firmat, like date9., or a char type.

A numeric date cannot handle a character 'E'.

Please run next code and post the result:

proc contents data=Gvkeytable; run;

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

shweta_234 — Wed, 29 Apr 2020 14:11:47 GMT

Hi i ran the mentioned code and results are
Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat
1 GVKEY Num 8 BEST12. BEST32.
3 LIID Num 8 BEST12. BEST32.
6 LINKDT Num 8 DATE9. DATE9.
7 LINKENDDT Char 9 $9. $9.
2 LINKPRIM Char 1 $1. $1.
4 LINKTYPE Char 2 $2. $2.
5 LPERMNO Num 8 BEST12. BEST32.
18 ajexq Num 8 BEST12. BEST32.
23 atq Num 8 BEST12. BEST32.
45 co_cik Num 8 BEST12. BEST32.
42 co_conm Char 27 $27. $27.
44 co_cusip Char 9 $9. $9.
47 co_naics Num 8 BEST12. BEST32.
46 co_sic Num 8 BEST12. BEST32.
43 co_tic Char 3 $3. $3.
17 conm Char 21 $21. $21.
33 conml Char 21 $21. $21.
13 consol Char 1 $1. $1.
31 costat Char 1 $1. $1.
24 cshfdq Char 1 $1. $1.
25 cshoq Num 8 BEST12. BEST32.
26 cshprq Num 8 BEST12. BEST32.
19 curcdq Char 3 $3. $3.
20 datacqtr Num 8 DATETIME. ANYDTDTM40.
8 datadate Num 8 DATE9. DATE9.
15 datafmt Char 3 $3. $3.
21 datafqtr Num 8 DATETIME. ANYDTDTM40.
27 epsfxq Num 8 BEST12. BEST32.
28 epspxq Num 8 BEST12. BEST32.
10 fqtr Num 8 BEST12. BEST32.
36 from Num 8 DATE9. DATE9.
9 fyearq Num 8 BEST12. BEST32.
11 fyr Num 8 BEST12. BEST32.
35 gvkeyx Num 8 BEST12. BEST32.
34 iid Num 8 BEST12. BEST32.
41 indexcat Char 3 $3. $3.
38 indextype Char 5 $5. $5.
12 indfmt Char 4 $4. $4.
14 popsrc Char 1 $1. $1.
32 prccq Num 8 BEST12. BEST32.
22 rdq Num 8 DATE9. DATE9.
29 saleq Num 8 BEST12. BEST32.
39 spii Char 1 $1. $1.
30 spiq Num 8 BEST12. BEST32.
40 spmi Num 8 BEST12. BEST32.
37 thru Char 1 $1. $1.
16 tic Char 4 $4. $4.

Re: ERROR 22-322: Syntax error, expecting one of the following: TABLE, VIEW. ERROR 76-322: Syntax e

Tom — Wed, 29 Apr 2020 14:22:45 GMT

Looks like you have a character variable.

7 LINKENDDT Char 9 $9. $9.

Most likely at some point you used to have a numeric variable that was using the .E special missing to indicate the on-going (no End date).

Do you want to keep it as a character variable?

case when (linkenddt in (' ','E','e')) then '31DEC9999' else linkenddt end

Or convert it to an actual date?

coalesce(input(linkenddt,?date9.),'31DEC9999'd) format=date9.