Different behavior in JMP 12 vs 11

Question

Hi, upon updating to JMP 12 there seems to have been a changed in the way dates are handled. More specifically: When browsing a SAS data set sitting on a server (Browse SAS data, select a library and a file, then build a query using the entry window) the entry form/(formula like window) does not accept the same kind of input as I used to be able to supply. For example I can write in JMP 11 in the formula entry as a part of a query, where other parameters may also be set:

datepart(datetime)='8jan2016'd

When I click 'OK' the entry gets accepted, but when I click 'RUN* there is an error: 22-322 saying, there is a syntax error.

This is quite unexpected, as it appears to be a quite low level thing. So what changed and what to do?

Regards

Poul Ravn Sørensen

Audrey_Shull · Answer

I can confirm that this was addressed in JMP 13.

Eric_Hill · Answer

Hey, Poul,

I can save you the trouble of contacting Tech Support - I was able to reproduce this problem.  It is indeed a bug in JMP 12 that we will look into for JMP 13.

As a workaround, I might suggest you try out the new SAS Query Builder that was introduced in JMP 12. You can invoke it from the ​File > SAS​ menu:

After you select the SAS connection you want to use, you'll get the ​Table Selector​ dialog:

on which you can select the table (or tables) that you want to query.  In this case, I just have one table (Egsamp.CARGO​), so I added that as the ​Primary​ table.  Then, click ​Build Query​ at the bottom.  That will bring up Query Builder​ proper.  Click ​Add All​ to add all of the columns from the table to the query, and then right-click on the date column you want to filter on, either in the ​Available Columns​ list or the ​Included Columns​ panel, and select ​Filter By​.

That will insert the default filter for the date column into the ​Filters​ panel:

To use the ​DatePart​ function, you will need to change this to a Custom Expression​ filter, which you can do from the red triangle menu:

That will just give you and entry field into which you can type in whatever criteria you want.  I typed in "datepart(Date) = '25Mar2000d'", and you can see that it worked by the Preview:

Just click ​Run Query​ at the bottom to import the filtered data.

Hope this helps.  We will look into the issue with SAS date literals in the Formula Editor.

Thanks,

Eric

Jeff_Perkinson · Answer

This does sound troubling.

You will probably get an answer more quickly by contacting Tech Support with the details. They can reproduce both environments and determine what's going on.

-Jeff