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

[shweta_234]

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?

[Shmuel]

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;

[shweta_234]

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

[shweta_234]

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

[DavePrinsloo]

'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

[shweta_234]

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?

[DavePrinsloo]

Look up the syntax to use CASE in your SQL select

[Shmuel]

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;

[shweta_234]

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.

[Tom · Accepted solution]

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.

[DavePrinsloo]

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

[Shmuel]

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;

[Shmuel]

the function is COALESCE