Help using Base SAS procedures

between statement not filtering out

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

between statement not filtering out



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

Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,919

Re: between statement not filtering out

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)

PG

View solution in original post


All Replies
Moderator
Posts: 252

Re: between statement not filtering out

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)

Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,919

Re: between statement not filtering out

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)

PG
Regular Contributor
Posts: 240

Re: between statement not filtering out


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
Respected Advisor
Posts: 4,919

Re: between statement not filtering out

As Andrew Howell suggested, please post a small sample of tables a, b, and c and the expected final output.
PG
Regular Contributor
Posts: 240

Re: between statement not filtering out

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
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 308 views
  • 0 likes
  • 3 in conversation