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

 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..... 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

7 REPLIES 7
Reeza
Super User

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

ballardw
Super User

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
Pyrite | Level 9

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
PROC Star

 

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ali_jooan
Obsidian | Level 7

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

ali_jooan
Obsidian | Level 7

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
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 1130 views
  • 6 likes
  • 5 in conversation