BookmarkSubscribeRSS Feed
mray
Calcite | Level 5

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

4 REPLIES 4
Sonywell
Fluorite | Level 6

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

mray
Calcite | Level 5

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

BarryDeCicco
Obsidian | Level 7

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.

vallsas
Pyrite | Level 9

Great. Thanks for solution.. appreciate

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2925 views
  • 2 likes
  • 4 in conversation