topic Viewing all values in SAS EG filter box in SAS Enterprise Guide

Viewing all values in SAS EG filter box

mray — Fri, 21 Aug 2015 15:03:36 GMT

Hello,

I have SAS production 9.3, SAS EG 5.1

I had connected to the data repository with the libname protocol..

Then I opened a table - CNTC - which has fields like -- contact_date, initiator_code, contact_key, etc...

Then when I use query builder and try to look for certain values in the Filter Tab (say for initiator_code ), it only shows 2 of the 5 or 6 distinct values that initiator_code actually has.

Moreover, when I try to filter on a date column, the last entry is some date in 2010 (after clicking on "more values" multiple times) and not dates in 2015 as I expect.

This data gets refreshed on a daily basis, so I know that there are dates in 2015 but I cannot see them in the SAS EG filter dialogue box. Even if I change default options in the query builder window, I do not know what should the right value be to display all dates in a table where the date begins in 1993. Too large a value crashes/stalls SAS EG.

I can type in a more recent date (which I cannot see but I expected it to be there because I know that the database has data from recent months) and it will work, however, that is not possible when there is a column with many kinds of values and one cannot guess what would be an appropriate value to filter (or search) on.

Can anyone tell me why these things are happening and how to get it to show all values in a column and all dates in a date field?

Thank you.

Monika

Re: Viewing all values in SAS EG filter box

Sonywell — Fri, 21 Aug 2015 18:37:54 GMT

Hi Monika,

The query builder doesn't scan the entire table to determine distinct values in a column when you select the show values button. By default EG 5.1 is set up to scan only up to 100,000 rows in an input source for performance reasons. You can change this setting by using Tools->Options->Query. In that dialog you can find the 'Maximum number of rows to process for distinct value requests' and it can be changed. This causes the SAS Server to have run through a lot more records when performing a distinct value request through query builder. With a lot of users doing this, it can become a drag on the system.

You can also use the Tasks->Describe->One Way frequencies and analyze distinct values of a column that way. This will not be limited by the option set in the same area.

Hope this helps,

Russ

Re: Viewing all values in SAS EG filter box

mray — Fri, 21 Aug 2015 18:50:20 GMT

Hello Russ

Thank you for your note.

I already tried the Tools->Options....I mentioned it in my previous post. Data size from date range1993 (it is a call centre/customer service data) to date is very large ~ 100M records. So distinct values on a date column doesn't make sense as there will be a value for every date from 1993 till today. Too big for the query. I was connecting to the data source and wanted to extract a subset of the data (for dates from jan 2015 through July 2015) Hence, I needed to set a date filter.

However, the other column (initiator_code) has 5-6 values within the first 100 records. (I am unable to copy paste the snapshots of those things in here).

I should have been able to see these distinct values in the default options, but couldn't. So changing the default options is not the solution.

Thank you.

Monika

Re: Viewing all values in SAS EG filter box

BarryDeCicco — Wed, 07 Feb 2018 21:55:16 GMT

Thanks! I had that problem; this solved it. I had thought that it was caused by the maximum number of rows to display, but I was wrong.

Re: Viewing all values in SAS EG filter box

vallsas — Thu, 12 Apr 2018 06:52:54 GMT

Great. Thanks for solution.. appreciate