|
View previous topic :: View next topic
|
| Author |
Message |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| No I dont think so check here |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Yes Vasanth 
Glad it worked...
I never used SAS since 2010  ...Hope I get to do some in future .. |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Ah..
2 years is fine. SAS is almost still the same "fun". |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
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 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 |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
| Glad to hear you've got it working! |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
|
|
|
