PROC SQL issue where MIN function is working but when changed to MAX returns nothing on timestam

[Reeza]

Without seeing the query it's kind of hard to say. Can you make some sample data that replicates your issue.

Otherwise my recommendation would be to switch to some form of a data step and use BY group processing to get the max/min.

[tugtrog]

Thanks for the reply, I just posted a code.

[Reeza]

Is it possible that coupled with the other conditions that you omit all records for some groups? Or that they're mutually exclusive in some manner?

My guess it would be O_TS=MIN and M_TS=Max() maybe never happens together?

[tugtrog]

So whats actually happening here is, There are 2 different sub queries, one selects MIN and the other selects MAX. So it should just select the MIN and then the MAX separately. Regardless of the other one. I get the correct values for almost all of the rows, this is just an example of one of the rows where it returned a MIN value and no MAX value. and the MIN will work in both but the MAX will not work in either sub query.

I hope that's clear

[Reeza]

Sounds to me like you have an edge case in your data that I again suspect you're losing with one of your other filters. Try removing them and see if you get results. It would definitely help to provide some sample data, especially for those edge cases.

[tugtrog]

Thanks a lot for you help, I think you are right and I am going to have to look deeper into it. I thought it might just be something I was overlooking. I appreciate your help.

[Tom]

You should post data for one of the problem cases so that we can help you debug.

You can cut down your code (and data) to just what is needed to show your issue.  

For example we don't need to see all of the variables you are selecting to see what your basic logic is.

Your description of the logic doesn't seem to match what the code is doing.

You said you where taking the MIN() and MAX(), but there are no MIN() or MAX() functions in the values you are selecting.

Instead you are using MIN() and MAX() in a HAVING clause.  One side effect of that is that your result set could have multiple observations due to ties.  So if the MAX(time) is '17:00't and there are three observations with that time then three observations will be selected.