Subscribe Bookmark RSS Feed

Different behavior in JMP 12 vs 11

poulravn

Community Trekker

Joined:

Jan 25, 2012

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


2 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

-Jeff
erichill

Staff

Joined:

Oct 1, 2013

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:

10737_sas_query_builder.png

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

10738_table_selector.png

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​.

10745_filter_by.png

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

10746_date_default_filter.png

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:

10747_custom_expression_menu.png

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:

10748_custexpr_filled_in.png

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