re: proc sql error

[twildone]

 Hi...I can't seem to correct why I am receiving an error message.....I am wondering if anyone can see where the error is. Thanks.

%LET StartDate = '2013-01-01 00:00:00.000';
%LET EndDate = '2017-12-31 23:59:59.999';

proc sql noprint;
	create table new as
		SELECT DISTINCT 
			S.StudentID, 
			S.LastName, 
			S.FirstName, 
			SP.MajorDegree, 
			SS.RegisterStatus, 
			S.DateEntered AS 'Matriculation Date'n, 
			S.WithdrawnDate, 
			(SELECT AdmissionDate FROM SQLDB.StudentDegree
				WHERE (StudentUID = S.StudentUID 
					AND MajorID1 = SP.MajorDegreeID) 
				ORDER BY GraduationDate DESC) AS AdmissionDate, 
			(SELECT GraduationDate FROM SQLDB.StudentDegree 
				WHERE (StudentUID = S.StudentUID 
					AND MajorID1 = SP.MajorDegreeID) 
				ORDER BY GraduationDate DESC) AS GraduationDate, 
			SS.EnrollmentStatus
	FROM  SQLDB.CAMS_StudentStatus_View SS
		INNER JOIN SQLDB.CAMS_Student_View S ON S.StudentUID = SS.StudentUID
		INNER JOIN SQLDB.CAMS_StudentProgram_View SP ON SP.StudentStatusID = SS.StudentStatusID
		INNER JOIN SQLDB.Programs PG ON S.ProgramsID = PG.ProgramsID
			WHERE S.DateEntered BETWEEN &StartDate AND &EndDate
				AND SP.MajorDegree ^= ''
			ORDER BY S.DateEntered, S.StudentID ASC;
quit;



Log output:

8         GOPTIONS ACCESSIBLE;
39         
40         %LET StartDate = '2013-01-01 00:00:00.000';
41         %LET EndDate = '2017-12-31 23:59:59.999';
42         
43         


44         proc sql noprint;
45         	create table new as
46         		SELECT DISTINCT
47         			S.StudentID,
48         			S.LastName,
49         			S.FirstName,
50         			SP.MajorDegree,
51         			SS.RegisterStatus,
52         			S.DateEntered AS 'Matriculation Date'n,
53         			S.WithdrawnDate,
54         			(SELECT AdmissionDate FROM SQLDB.StudentDegree
2                                                          The SAS System                            12:53 Thursday, October 5, 2017

55         				WHERE (StudentUID = S.StudentUID
56         					AND MajorID1 = SP.MajorDegreeID)
57         				ORDER BY GraduationDate DESC) AS AdmissionDate,
               _____
               22
               76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, 
              NOTIN, OR, OUTER, UNION, ^, ^=, |, ||, ~, ~=.  

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

58         			(SELECT GraduationDate FROM SQLDB.StudentDegree
59         				WHERE (StudentUID = S.StudentUID
60         					AND MajorID1 = SP.MajorDegreeID)
61         				ORDER BY GraduationDate DESC) AS GraduationDate,
62         			SS.EnrollmentStatus
63         	FROM  SQLDB.CAMS_StudentStatus_View SS
64         		INNER JOIN SQLDB.CAMS_Student_View S ON S.StudentUID = SS.StudentUID
65         		INNER JOIN SQLDB.CAMS_StudentProgram_View SP ON SP.StudentStatusID = SS.StudentStatusID
66         		INNER JOIN SQLDB.Programs PG ON S.ProgramsID = PG.ProgramsID
67         			WHERE S.DateEntered BETWEEN &StartDate AND &EndDate
68         				AND SP.MajorDegree ^= ''
69         			ORDER BY S.DateEntered, S.StudentID ASC;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
70         quit;

Hi..... 

[Reeza]

ORDER BY on subqueries don't make sense, I would start by removing them.

[ballardw · Accepted solution]

Have you verified that the subquery

(SELECT AdmissionDate FROM SQLDB.StudentDegree55

WHERE (StudentUID = S.StudentUID

AND MajorID1 = SP.MajorDegreeID)

ORDER BY GraduationDate DESC)

returns only a single value?

I believe the requirement for a subquery as a part of the select statement can return only a single value.

[twildone]

Hi Ballardw.....thank you for suggestion....I did remove the order by in the sub query with the select statement and receive a different error message which most likely was due to not being able to return a single value. I ended up breaking the queries into separate proc sql procedures and then joining the query results together. Thanks once again....greatly appreciated your help.

[mkeintz]

I'm not saying there is anything wrong with your other responses, but I believe error message is caused by the way you assign a value to the macrovars, and the way you use them later. 

You have

    %let startdate='2013-01-01 00:00:00.000';

    %let enddate='2017-12-31 23:59:59:999';

Later on you have the expression

  WHERE S.DateEntered BETWEEN &StartDate AND &EndDate

if DateEntered is a character variable, then ignore my comments.  But if DateEntered is a sas datetime variables, then use this:

%LET StartDate = 2013-01-01 00:00:00.000;
%LET EndDate = 2017-12-31 23:59:59.999;

proc sql ...

    WHERE S.DateEntered BETWEEN "&StartDate"dt AND "&EndDate"dt
	...

This approach allows you to properly use macrovars to specify a datetime literal.

1. In the %LET statement, don't use any quotes.
2. Refer to those macrovars  within double quotes and end with the literal type (dt for datetime).  Double quotes allows the macroprocessor to resolve the STARTDATE and ENDATE text in the executable prior to datetime conversion.  Single quotes would preserve the text '&startdate'.  That's probably why you put the single quotes in the %let statement.

[ali_jooan]

I think order by will not work in the nested Select statements. Try removing it and run.

[ali_jooan]

also you may need to make sure the sub query doesn't return more than one records, guess it wont give you errors but it may get you unexpected results

[twildone]

Hi Mkeintz.....thanks for your suggestion and explanation....it worked.