DATA Step, Macro, Functions and more

Error - PROC SQL using BETWEEN

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Error - PROC SQL using BETWEEN

[ Edited ]
243            PROC SQL;                                                        
244               CREATE TABLE MHTEMP AS                                        
245                 SELECT                                                      
246                   A.*,                                                      
247                   B.ADMSVCDT AS ADMITDATE2                                  
248                 FROM                                                        
249                   MH2 AS A, ADMITDT AS B                                    
250                 WHERE A.BENE = B.BENE                                       
251                       AND A.LBEGDT BETWEEN B.ADMSVCDT = ADMITDATE2;         
                                                          _                     
                                                          _                     
                                                          _                     
                                                          22                    
                                                          22                    
                                                          22                    
                                                            __________          
                                                            __________          
                                                            __________          
9                                                          THE SAS SYSTEM       
                                                                                
                                                            202                 
                                                            202                 
                                                            202                 
ERROR 22-322: SYNTAX ERROR, EXPECTING ONE OF THE FOLLOWING: !!, *, **, +, -, /, 
ERROR 22-322: SYNTAX ERROR, EXPECTING ONE OF THE FOLLOWING: !!, *, **, +, -, /, 
ERROR 22-322: SYNTAX ERROR, EXPECTING ONE OF THE FOLLOWING: !!, *, **, +, -, /, 
                                                                                
ERROR 202-322: THE OPTION OR PARAMETER IS NOT RECOGNIZED AND WILL BE IGNORED.   
ERROR 202-322: THE OPTION OR PARAMETER IS NOT RECOGNIZED AND WILL BE IGNORED.   
ERROR 202-322: THE OPTION OR PARAMETER IS NOT RECOGNIZED AND WILL BE IGNORED.   
                                                                                
NOTE: PROC SQL SET OPTION NOEXEC AND WILL CONTINUE TO CHECK THE SYNTAX OF STATEM
252                 QUIT;                                                       
NOTE: THE SAS SYSTEM STOPPED PROCESSING THIS STEP BECAUSE OF ERRORS.            
NOTE: THE PROCEDURE SQL USED 0.00 CPU SECONDS AND 16265K.                       
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 1644K BELOW THE LINE AND 22188K AB
                                                                                
253                                                                             
                                                                                
                                                                                
254                 DATA MHTEMP2 (DROP=ADMITDATE2);                             


                                                                               

 


Accepted Solutions
Solution
‎05-05-2017 01:02 PM
Occasional Contributor
Posts: 8

Re: New error

You need to use "AND" instead of the equal sign when using BETWEEN.

View solution in original post


All Replies
Solution
‎05-05-2017 01:02 PM
Occasional Contributor
Posts: 8

Re: New error

You need to use "AND" instead of the equal sign when using BETWEEN.

Occasional Contributor
Posts: 8

Re: New error

As a follow up question, what are you trying to do in your join?  I understand

A.BENE = B.BENE

 

where I get lost is the next part:

 

A.LBEGDT BETWEEN B.ADMSVCDT = ADMITDATE2; 

 

Is ADMITDATE2 a column in the table ADMITDT or are you trying to refer to the name you gave B.ADMSVCDT in the select statement?

If the answer is yes to the first one, then I find it odd that you would rename a different column in ADMITDT to that column name, although syntatically correct, it's not a good practice.

If the answer is yes to the second question, then why are you using "BETWEEN", when "A.LBEGDT = B.ADMSVCDT" would work.

 

But since you used "BETWEEN", I think that the answer is actually no to bother questions; and you were trying to select records that are between two dates, such as an admission date and a discharge date.  In this case, make sure you double check the column names of the two date columns.

Super User
Posts: 17,930

Re: Error - PROC SQL using BETWEEN

The syntax is incorrect. 

 

If you're doing a join have all the join conditions first (=) and the filters after, ie the BETWEEN. 

 

As is, the following is the issue.

 

 B.ADMSVCDT = ADMITDATE2

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 149 views
  • 0 likes
  • 3 in conversation