between statement not filtering out

[BETO]

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

[AndrewHowell]

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)

[PGStats · Accepted solution]

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)

[BETO]

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

[PGStats]

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

[BETO]

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