Date: Fri, 24 Jun 2005 10:31:05 +1200
Reply-To: robin.templer@xtra.co.nz
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Robin & Charmaine Templer <robin.templer@XTRA.CO.NZ>
Subject: Re: WHERE clause limits
Content-Type: text/plain; charset=ISO-8859-1
Really must get out of the habit of answering sas-l late at night.
It was 8.2 on HPUX. We were trying to tune a SQL query which was taking 60+ minutes, using put(a,fmt.)='Y' in the where - we tried changing to use in(1,2,3,4,56....) but could not, due to the unknown number of values (non -consecutive) and the random dropping of some of the values.
We ended up changing the query to a join - 15 minutes.
>
> From: "Michael S. Zdeb" <msz03@health.state.ny.us>
> Date: 2005/06/24 Fri AM 01:00:09 GMT+12:00
> To: robin.templer@xtra.co.nz
> CC: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: WHERE clause limits
>
>
> Hi...WHERE does the 140 value limit come from...using V9.1.3 on a Windows
> XP PC...here are 1,000 values (10,000 also works)...
>
> filename x temp;
>
> data test;
> file x;
> put "data new; set test; where j in (";
> do j=1 to 1000;
> output;
> put j;
> end;
> put "); run;";
> run;
>
> %include x;
>
> The LOG..
>
> NOTE: There were 1000 observations read from the data set WORK.TEST.
> WHERE j in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
> 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35,
> 36, 37,
> 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54,
> 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73,
> 74, 75,
> 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92,
> 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108,
> 109, 110,
> 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124,
> 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139,
> 140,
> 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154,
> 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169,
> 170,
> 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184,
> 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199,
> 200,
>
> <many more lines>
>
> 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214,
> 215, 216, 217, 218, 219, 220, 911, 912, 913, 914, 915, 916, 917, 918, 919,
> 920,
> 921, 922, 923, 924, 925, 926, 927, 928, 929, 930, 931, 932, 933, 934,
> 935, 936, 937, 938, 939, 940, 941, 942, 943, 944, 945, 946, 947, 948, 949,
> 950,
> 951, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964,
> 965, 966, 967, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977, 978, 979,
> 980,
> 981, 982, 983, 984, 985, 986, 987, 988, 989, 990, 991, 992, 993, 994,
> 995, 996, 997, 998, 999, 1000);
> NOTE: The data set WORK.NEW has 1000 observations and 1 variables.
> NOTE: DATA statement used (Total process time):
> real time 0.11 seconds
> cpu time 0.12 seconds
>
>
> Mike Zdeb
> U@Albany School of Public Health
> 1 University Drive
> Rensselaer, NY 12144-3456
> (P)518-402-6479
> (F)630-604-1475
>
>
>
> |---------+---------------------------->
> | | Robin & Charmaine|
> | | Templer |
> | | <robin.templer@xt|
> | | ra.co.nz> |
> | | Sent by: "SAS(r) |
> | | Discussion" |
> | | <SAS-L@LISTSERV.U|
> | | GA.EDU> |
> | | |
> | | |
> | | 06/22/2005 11:19 |
> | | PM |
> | | Please respond to|
> | | robin.templer |
> | | |
> |---------+---------------------------->
> >--------------------------------------------------------------------------------------------------------------|
> | |
> | To: SAS-L@LISTSERV.UGA.EDU |
> | cc: |
> | Subject: Re: WHERE clause limits |
> >--------------------------------------------------------------------------------------------------------------|
>
>
>
>
> Well - I know for certain that a where clause using IN will not take 140
> values!! What was concerning was that it seemed to drop random values out
> of the clause - but I think that this is more to do with the IN operator
> than the where clause itself.
>
> >
> > From: thelaker@GMAIL.COM
> > Date: 2005/06/23 Thu PM 02:37:52 GMT+12:00
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Re: WHERE clause limits
> >
> > > Is there any chance that whatever length limit is operating here
> applies
> > > only to the clause that gets echoed back in these NOTEs? Or are you
> > > actually getting data in your dset that don't meet your full WHERE
> > > clause?
> > >
> >
> > It's the opposite of your last statement. I was failing to get
> > observations in the claims_subset dataset that should have been
> > included if the full WHERE clause had been implemented. When testing
> > this on real data, I converted this WHERE clause to an IF statement
> > (changing all the BETWEENs to 'value'<=variable<='value' expressions)
> > and got all the observations I expected in claims_subset.
> >
>
>
>
| 
