topic Re: between statement not filtering out in SAS Procedures

between statement not filtering out

BETO — Wed, 09 Sep 2015 22:15:05 GMT

proc sql;

create table temp as

select a.*,b.Machine as b_Machine,b.date as b_date

from a,b

where a.Machine=b.Machine and b.date gt a.date

group by a.Machine

having b.date-a.date=min(b.date-a.date);

create table want as

select temp.*,c.date as c_date,

case

when c.date between temp.date and temp.b_date then 'yes'

else 'no '

end as flag

from temp left join c on

temp.Machine=c.Machine ;

quit;

My want table has a where statement where the c_date
Falls between temp date and temp date ....but it's not it's bringing in every instance from c table ...I don't need every instance from c only if it falls between those 2 dates an if not than blank..thanks

Re: between statement not filtering out

AndrewHowell — Wed, 09 Sep 2015 23:39:59 GMT

I'm guessing either or both of:

Mixes of dates & datetimes
The Join failing to populate one of the date values

Difficult to diagnose without seeing the data. Can you either:

Post a subset of data so we can review, or;
Attempt to replicate the issue using one of the sample SAS data sets in the SASHELP library, which we could all then use to diagnose the issue.

Also, if your issue is specifically a CASE/BETWEEN issue, can I suggest you leave out the other statements (joins, create table, etc)

Re: between statement not filtering out

PGStats — Thu, 10 Sep 2015 02:05:25 GMT

Try this instead as your second query :

create table want as
 select temp.*,c.date as c_date,
    case 
     when c.date is missing then 'no'
     else 'yes'
    end as flag                       
  from temp left join c on
   temp.Machine=c.Machine and c.date between temp.date and temp.b_date;

(untested)

Re: between statement not filtering out

BETO — Thu, 10 Sep 2015 13:40:27 GMT

the first query works it brings in 82 rows of data .I need to run it thru the 3rd table to see if there was a make up service an when it runs it's bring in every instance from the 3rd table it increase output to 350 rows here is example
Output 1st query.                                                           Output 2nd query
Machine.     Date.       Machine     date                   Machine      date.              flag
A1.             09/05/15.      A1.            09/07/15.               A1.            09/07/15.      yes
A1.             09/5/15.        A1.           09/07/15.                A1.         09/15/15.     No
A1.                09/05/15.   A1.           09/07/15.               A1.         09/24/15.      No
It's bring every instance from the 3rd table if it would have stop in the first it would be perfect in that instance
Here is and example of what I'm looking for
Output 1st query.                                                           Output 2nd query
Machine.     Date.       Machine     date                   Machine      date.               Flag
A1.             09/05/15.      A1.            09/07/15.               A1.            09/07/15.          Yes
A2.             09/09/15.      A2.            09/14/15.               A2.            09/16/15.         No
A3.             09/2/15.        A3.           09/06/15.                A3.            09/04/15.         Yes
A1.             09/11/15.      A1              09/15/15.                A1.            09/18/15.            No

The first query works great I just need to run it thru 3rd. Table to make sure there was no make up service.. An what's happening it's bringing in everything from the 3rd table instance so if machine a1 appears 10x in 3rd table it brings all 10x over. I just need the one instance if it falls between those two dates if not. Than I don't need the other instance to come with .... Thanks

Re: between statement not filtering out

PGStats — Thu, 10 Sep 2015 15:21:42 GMT

As Andrew Howell suggested, please post a small sample of tables a, b, and c and the expected final output.

Re: between statement not filtering out

BETO — Thu, 10 Sep 2015 16:33:41 GMT

Hi PGSTATS ,
I provided an example above of 3 tables and there structure. ..the temp query works fine the want seems to bring everything in from the 3rd table ....thanks