Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SAS dateset option - WHERE clause usage with BETWEEN

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Fri Nov 23, 2012 6:00 pm    Post subject: SAS dateset option - WHERE clause usage with BETWEEN
Reply with quote

Hi,
I would like to know how to use the WHERE clause dataset option along with between operator. Could you please correct the below dataset option so that the program selects only records between 2nd Oct and 5th Oct.

I tried the below program.
Code:
DATA THINK;                                       
INPUT DATE1 DATETIME.;                           
CARDS;                                           
01OCT12:00:00                                     
02OCT12:00:00                                     
03OCT12:00:00                                     
04OCT12:00:00                                     
05OCT12:00:00                                     
06OCT12:00:00                                     
;                                                 
RUN;                                             
PROC PRINT DATA = THINK(WHERE = (DATE1           
BETWEEN('02OCT12:00:00'DT  AND  '05OCT12:00:00'DT)));
RUN;   


but it leads to error,
Code:
ERROR 22-322: Missing ')' parenthesis for data set option list


Thanks in advance,
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Nov 23, 2012 6:14 pm    Post subject:
Reply with quote

Should this be
Code:

PROC PRINT DATA = THINK(WHERE DATE1= (DATE1           
BETWEEN('02OCT12:00:00'DT  AND  '05OCT12:00:00'DT)));
RUN;   


????
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Nov 23, 2012 6:33 pm    Post subject:
Reply with quote

Sorry I was wrong last time

Can you try this

Code:
PROC PRINT DATA = THINK;
WHERE DATE1           
BETWEEN '02OCT12:00:00'DT  AND  '05OCT12:00:00'DT;
RUN;
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Fri Nov 23, 2012 6:35 pm    Post subject:
Reply with quote

Hi Pandora,
WHERE option requires = immediately after the keyword.

Code:
13   PROC PRINT DATA = THINK(WHERE DATE1= (DATE1
                             ----- -----       
                             12    12           
                                   22           
ERROR 12-63: Missing '=' for option WHERE.     
ERROR 22-7: Invalid option name DATE1.         
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Fri Nov 23, 2012 6:41 pm    Post subject:
Reply with quote

Hi Pandora,
Thanks for your suggestion. It works fine.
But the requirement is to do this with dataset option instead of a separate where statement.

The reason being dataset option applies to individual datasets and not to all datasets under the data step or proc step.

Thanks & Regards,
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Nov 23, 2012 6:41 pm    Post subject:
Reply with quote

No I dont think so check here
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Fri Nov 23, 2012 6:50 pm    Post subject:
Reply with quote

Hi Pandora,
Thanks for your view.
There is a shortcoming with where statement when compared with where dataset option.
Thats the reason which lead to this topic


Code:
DATA ONE; 
INPUT VAR;
CARDS;     
1         
2         
3         
4         
5         
;         
RUN;       
DATA TWO; 
INPUT VAR;
CARDS;     
10         
20         
30         
40         
50         
;         
RUN;       
DATA OUTPUT;                                 
SET ONE(WHERE=(VAR > 2)) TWO(WHERE=(VAR >20));
RUN;                                         
PROC PRINT DATA = OUTPUT; RUN;               


Quote:
The reason being dataset option applies to individual datasets and not to all datasets under the data step or proc step.


Hope this clarifies things.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Nov 23, 2012 6:55 pm    Post subject:
Reply with quote

Yes Vasanth icon_smile.gif

Glad it worked...

I never used SAS since 2010 icon_sad.gif ...Hope I get to do some in future ..
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Fri Nov 23, 2012 7:15 pm    Post subject:
Reply with quote

Ah..
2 years is fine. SAS is almost still the same "fun".
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Fri Nov 23, 2012 7:28 pm    Post subject:
Reply with quote

Vasanth, I found this on the SAS web site:
Quote:
Syntax
WHERE=(where-expression-1<logical-operator where-expression-n>)


Syntax Description

where-expression

is an arithmetic or logical expression that consists of a sequence of operators, operands, and SAS functions. An operand is a variable, a SAS function, or a constant. An operator is a symbol that requests a comparison, logical operation, or arithmetic calculation. The expression must be enclosed in parentheses.
logical-operator

can be AND, AND NOT, OR, or OR NOT.
at http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000131192.htm so I suspect if you change the clause to use GE and LE you can get it to work but not with BETWEEN.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Mon Nov 26, 2012 8:57 pm    Post subject:
Reply with quote

Hello Robert,
Thank you for your inputs. The IN operator was working, So assumed that other options like BETWEEN also might work.

Anyway, got it sorted out with your GE and LE idea.

Regards,
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Mon Nov 26, 2012 9:17 pm    Post subject:
Reply with quote

Glad to hear you've got it working!
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Tue Nov 27, 2012 9:21 am    Post subject:
Reply with quote

Update:
Got the where dataset option working with "between" operator :-)
Code:
DATA THINK;                                       
INPUT DATE1 DATETIME.;                           
CARDS;                                           
01OCT12:00:00                                     
02OCT12:00:00                                     
03OCT12:00:00                                     
04OCT12:00:00                                     
05OCT12:00:00                                     
06OCT12:00:00                                     
;                                                 
RUN;                                             
PROC PRINT DATA = THINK(WHERE = (DATE1 BETWEEN '02OCT12:00:00'DT  AND  '04OCT12:00:00'DT));
FORMAT date1 datetime.;
RUN; 

Regards,
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Compiler option for override the exte... muralikrishnan_new COBOL Programming 9 Fri Jul 08, 2016 12:09 pm
No new posts ICETOOL higher option Anshul Das DFSORT/ICETOOL 14 Thu Apr 14, 2016 8:00 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us