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?
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.
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;
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;
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.
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;
the function is COALESCE
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.