topic Re: PROC SQL issue where MIN function is working but when changed to MAX returns nothing on timestam in New SAS User

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

tugtrog — Thu, 25 Oct 2018 21:03:47 GMT

I am trying to write a query that will select MIN and MAX timestamp on a single day. I am able to get a vast majority of my data to pull, but there are issues on a few of the rows where I can pull the MIN date but when I change it to MAX I get a page break as the results. There are multiple timestamps per day, but if not it still should select the same timestamp for MIN and MAX.

My code is extremely long or I would post it.

I am using a having statement to select the min and max similar to this:

proc sql;

SELECT distinct t2.no,
					T1._ID,
					t2.id, 
					t3.First_Name
					t3.last_name
					t3.TITLE, 
					t3.02_LOCATION
					t3.Level_01, 
					t3.Level_02, 
					T1.M_TS
					T1.O_TS
					t3.employee
					T1.O_A
				FROM TEST.DAY T2
					INNER JOIN B.LE T1 ON (T2.ID = T1._ID and (PUT(DATEPART(T2.ADate),date9.) = PUT(DATEPART(T1.M_TS),date9.)))
					INNER JOIN B.E T3 ON (T1.O_A = T3.EMPLOYEE AND (t3.O_TS > '15Dec9999:0:0:0'dt))
					WHERE T2.NO = '11111-11111'
						GROUP BY T1._ID
							HAVING M_TS = MAX(T1.M_TS) and 
								O_TS = MIN(T1.O_TS) AND
								FIRST_NAME = scan(t3.first_name,1,' ') AND 
								LAST_NAME = SCAN(T3.LAST_NAME,1,' ')
;
run;

I change nothing in the code but the MAX to MIN and the code works. Does anyone have an idea of what could cause this issue?

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

novinosrin — Thu, 25 Oct 2018 20:52:59 GMT

Full code, log and error info plz

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

Reeza — Thu, 25 Oct 2018 21:04:44 GMT

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.

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

tugtrog — Thu, 25 Oct 2018 21:05:29 GMT

I posted the code, but I am not getting an error I am just having nulls returned in the columns when I put it in a created table, and page brake when looking at results.

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

tugtrog — Thu, 25 Oct 2018 21:06:54 GMT

Thanks for the reply, I just posted a code.

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

Reeza — Thu, 25 Oct 2018 21:07:30 GMT

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?

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

tugtrog — Thu, 25 Oct 2018 21:13:10 GMT

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

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

novinosrin — Thu, 25 Oct 2018 21:13:47 GMT

Got it.

HAVING M_TS = MAX(T1.M_TS) and 
								O_TS = MIN(T1.O_TS) AND
								FIRST_NAME = scan(t3.first_name,1,' ') AND 
								LAST_NAME = SCAN(T3.LAST_NAME,1,' ')
;

Nothing wrong with the above syntactically. From a business logic or data point of view can't tell. However, simple conclusion is that no records satisfy all the conditions specified in "having" filter.

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

Reeza — Thu, 25 Oct 2018 21:15:17 GMT

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.

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

tugtrog — Thu, 25 Oct 2018 21:17:37 GMT

Thanks a lot for your response and time. I think it might be an issue in the data, but I figured I would check here to see if it was something else I was missing.

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

tugtrog — Thu, 25 Oct 2018 21:20:53 GMT

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.

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

novinosrin — Thu, 25 Oct 2018 21:39:31 GMT

MH 2 cents thought:

If you are at an organization that uses SAS at enterprise level on a commercial level, I would rather prefer to deal a lot of this in piecemeal preferably taking advantage with a datastep approach unless something is preferred by egoistic imbecile bosses who think they have achieved a great deal after an explicit sql connect pass through writing tons of variable names with the potential leading to typos and plus the risk of "remerge" that may cause so much duplication and other unwarranted intricacies causing inaccuracy and integrity issues.

Taking advantage of the beautiful PDV block with exquisite block of addresses that holds the operands reading the descriptor portion giving us the breathing space to play with variables of interest for create, insert, update, delete alone is priceless while the other variables can just sleep and still be neatly written to the output buffer.

Of course, I love proc sql and have/will use when it's not unwieldy and that involves cleaner column processing involving less lexicon. Sure Proc Sql also has to have a memory address block and even the great guru Paul Dorfman has mentioned the APP functions can be put to effect in proc sql which might of my personal interest at some point in the future. Nevertheless, long select clauses with many nested in it will certainly cause someone a heartache for sure. HTH

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

kiranv_ — Thu, 25 Oct 2018 23:09:03 GMT

@novinosrin I love your answers and learn from you, i agree that Data step gives more flexibility in terms of Data processing but

1. Explicit sql has significant impact in run times and they are very important when they are lots of records.

2. Other than Proc sql , no other SQL implementation(as far as I know) allow remerge but multiple passes

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

novinosrin — Thu, 25 Oct 2018 23:11:50 GMT

@kiranv_ --"1. Explicit sql has significant impact in run times and they are very important when they are lots of records."

Not necessarily. I will prove that to you in time. Unfortunately not at 6:10 chicago time while on a hurry to catch red line CTA to get back home.

I agree to 2.

Finally Thank you for the kind note.

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

kiranv_ — Thu, 25 Oct 2018 23:58:01 GMT

sure. I worked on code to process close to 200 millions records with close to 3000 lines of SAS code to Teradata sql, difference was from 20 min to 18 hours. major time issue is because of I/0

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

Tom — Fri, 26 Oct 2018 00:45:55 GMT

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.

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

novinosrin — Fri, 26 Oct 2018 01:26:06 GMT

That depends upon the quality of SAS user and not the product/language plus taking into account he objective/need.and optimum approaches.

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

Reeza — Fri, 26 Oct 2018 01:33:43 GMT

@novinosrin wrote:
That depends upon the quality of SAS user and not the product/language plus taking into account he objective/need.and optimum approaches.

Usually it has to do with running on a server versus running on a desktop is my guess.

It's a good workaround when you can't afford a SAS server, but need the power of a server. You are limited by the type of work though, because it's mostly standard data manipulation. Until SQL introduced PIVOT (2008ish) and some of the more statistical techniques it was more of an issue.

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

novinosrin — Fri, 26 Oct 2018 02:39:24 GMT

Thank you @Reeza. Well noted, The discussion might next slip into performance of proc sql vs datastep(libname) at engine level as the latter indeed has a higher overhead measuring direct extracts/in general column processing as opposed to row, fewer joins/set operators/indexes (more flexible in proc sql than datastep or even proc datasets for obvious reasons. Reference; complete guide to indexes - by author Micheal raithel) utmost.

Datastep is indeed powerful for various tasks that can be accomplished. However, saying that how extensive, variety/complexity it can get pretty much seems unlimited especially to the readers eye on this very forum when demonstrated by some whom we look up to as role models.( don't wanna keep repeating the ones I adore lol)

The matter of typing and maintaining convenience, control and the kind of objective./business requirement beyond the set of functionality described in paragraph one by proc sql and the control of handling variables to the tune of a SAS user be row level processing or column level processing(I must emphasize the required proficiency hash, array(multi) and dow etc) is what makes SAS the classy gem, a kind of its own and yet one can be obliterate to the descriptors(meta portion) i.e not having to worry about the variables that's that not required for processing which doesn't hinder.

On the other hand in proc sql, if fewer select clauses with small number of vars is fine, however long clauses with a huge list of variables, and joins made on multiple variables(with several AND operations) , aliases, ambiguous references and many more unnecessary nits to take care of. And on top of the that, Cartesian is another sweetheart to beware.

So to conclude, besides the server and other things, my view is that different objectives may warrant efficient approaches that would test the individual super user to his/her merits in coming up with the most optimum quality solution.

Best Regards!

PS IMHO, if SAS didn't provide the scope of competitiveness to pursue further far and wide, the charm and joy of coding wouldn't exist. 🙂 Lastly, I use sql when that happens to be the most laziest option to type(code) yet comparable to other options.

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

tugtrog — Fri, 26 Oct 2018 14:57:04 GMT

This really helped, thank you!