SAS analyze data set for best variables index candidates

teelov — Wed, 03 Oct 2018 14:28:00 GMT

i have an number of datasets, ranging from 1000 rows to 300,000,000, 10 variables to 256

on a proceedings paper i read that:

1 % - 15% An index will definitely improve program performance

16% - 20% An index will probably improve program performance

so with that in mind i need to ask the question and create some code which:

Returns a list of variables that meet the criteria where then number of distinct values on given variables that amount to 20% or less of the total row count of that table

I'm not sure how to approach this as it will take some stress on the server if i just use proc freq?

advice?

Re: SAS analyze data set for best variables index candidates

ballardw — Wed, 03 Oct 2018 14:56:55 GMT

@teelov wrote:

i have an number of datasets, ranging from 1000 rows to 300,000,000, 10 variables to 256

on a proceedings paper i read that:

1 % - 15% An index will definitely improve program performance

16% - 20% An index will probably improve program performance

so with that in mind i need to ask the question and create some code which:

Returns a list of variables that meet the criteria where then number of distinct values on given variables that amount to 20% or less of the total row count of that table

I'm not sure how to approach this as it will take some stress on the server if i just use proc freq?

advice?

I would be tempted to start with proc freq with the nlevels option which reports the number of non-missing and missing levels for variables. Candidates would be those that have no missing levels and relatively large number of non-missing levels.

Example:

ods select nlevels;
Proc freq data=sashelp.class nlevels;
run;

The ods select only displays the neleves information. You can use the ODS OUTPUT to save that into a data set if needed.

I would look for variables that are more categorical in nature, such as NAME in the above, than measured such as HEIGHT.

topic SAS analyze data set for best variables index candidates in SAS Programming

SAS analyze data set for best variables index candidates

Re: SAS analyze data set for best variables index candidates