BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shweta_234
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

12 REPLIES 12
Shmuel
Garnet | Level 18

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
Fluorite | Level 6
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
Fluorite | Level 6
the variable linkenddt has dates of format 30-Jun-78 and in some observations it is 'E".
DavePrinsloo
Pyrite | Level 9
'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
Fluorite | Level 6
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
Pyrite | Level 9
Look up the syntax to use CASE in your SQL select
Shmuel
Garnet | Level 18

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
Fluorite | Level 6
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
Super User Tom
Super User

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
Pyrite | Level 9
and you need to spell correctly! COASLESCE is actually COALESCE.
I suggest you check these simple errors before posting!
Shmuel
Garnet | Level 18

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 12 replies
  • 3150 views
  • 1 like
  • 4 in conversation