sas - sql error

bumchum — Fri, 16 Apr 2021 03:21:39 GMT

proc sql;
title 'Employees with more than 30 Years of service as of December 31,2007';
select employee_name, intck('Year',employee_hire_date,'31dec2007'd) format=2. 
YOS as 'Year of Service', mgr (select employee_name 
from orion.employee_addresses 
where a.employee_id=employee_organization.manager_id)
from orion.employee_addresses a ,orion.employee_payroll p
where a.employee_id=p.employee_id
having Year of Service >30
order by employee_name;
quit;
title;

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc sql;

74 title 'Employees with more than 30 Years of service as of December 31,2007';

75 select employee_name, int('31dec2007'd-employee_hire_date/365.25) format=2.

76 YOS as 'Year of Service',mgr (select employee_name

___ _____________

22 76

202

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,

LENGTH, TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

76 ! YOS as 'Year of Service',mgr (select employee_name

_____________

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,

BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||,

~, ~=.

77 from orion.employee_addresses

78 where a.employee_id=employee_organization.manager_id)

79 from orion.employee_addresses a ,orion.employee_payroll p

80 where a.employee_id=p.employee_id

81 having Year of Service >30

82 order by employee_name;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

83 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

Re: sas - sql error

Tom — Fri, 16 Apr 2021 04:01:42 GMT

That does not look like valid code. Let's look at it piece by piece.

Starts out ok. Although why you would use SQL to print the results instead of making a dataset (or view) of the results and then printing them using normal printing tools like PROC PRINT or PROC REPORT.

proc sql;
title 'Employees with more than 30 Years of service as of December 31,2007';
select employee_name

The start of the line looks sort of ok

, intck('Year',employee_hire_date,'31dec2007'd) format=2. YOS as 'Year of Service'

Not sure why you feel a need to attach a format to an integer. If the values is ever larger then 99 or smaller than -9 then trying to print it with only two characters will fail. The AS keyword goes before the name you want to give the variable not after.

And why not use the LABEL= keyword to attach the label?

This part makes no sense at all.

, mgr (select employee_name from orion.employee_addresses where .employee_id=employee_organization.manager_id)

This part seems normal

from orion.employee_addresses a
   , orion.employee_payroll p
where a.employee_id=p.employee_id

This is strange

having Year of Service >30

You cannot just list three variable names in a row without any operators between them. Did you mean to reference the second column in your select statement that you appear to be trying to name as YOS?

The last clause looks fine.

order by employee_name;

topic Re: sas - sql error in SAS Programming

sas - sql error

Re: sas - sql error