topic Re: Index files? in SAS Programming

Index files?

kjohnsonm — Wed, 01 Jul 2020 18:44:26 GMT

I am confused about Indexes. I think I need them on a particularly large file but the online help info is leaving me scratching my head.

"The Index File

The index file is a SAS file that has the same name as its associated data file, and that has a member type of INDEX. There is only one index file per data file. That is, all indexes for a data file are stored in a single file.

The index file might be a separate file, or be part of the data file, depending on the operating environment. In any case, the index file is stored in the same SAS library as its data file."

from this documentation page.

So I am working on Windows10 PC saving my data on our file server. I read in a 4.5 Million obs data set, and add indexes to this data set, check with the "options msglevel=I;" and a proc contents my data set, I see my indexes listed as expected. I do a simple data step write to the file server, but do not see an index file, no problem this help says it can be internal. I close my program and write a simple read data set program and check proc contents and the indexes are gone. Have I missed something?? Shouldn't the index be internal and available to my second program? Did i miss an option?

Re: Index files?

ballardw — Wed, 14 Feb 2018 00:06:58 GMT

Can you show the code of how you read the data and added the index? And how you did the "simple data step write to the file server"?

I suspect that you did not COPY the data set which would preserve the index but did something like:

data servlib.mydata;

set myindexeddataset;

run;

which creates a new data set without an index.

Re: Index files?

kjohnsonm — Wed, 14 Feb 2018 00:12:37 GMT

options nocenter;options ls=256;title1 ;footnote1 ;
options mlogic mprint merror symbolgen;
options msglevel=I;
libname adm odbc dsn=adm schema = dbo;
%Let FName = %SysGet( SAS_EXECFILEPATH ) ;
%put &fname;
%let sasfile= %sysget(SAS_EXECFILEname);
%put &sasfile ;
%Let PName = %qsubstr(%sysget(SAS_EXECFILEPATH),1, %length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname))) ;
%put &pname;
%let currentfolder=%scan(&pname,1,'\',B);
%put &currentfolder.;
%let parentpath=%sysfunc(tranwrd(&pname,&currentfolder.\,));
%put &parentpath.;
libname data "&pname";

/* I have dumped a large amound of fields just for shortening this up */

proc sql;
create table UGRD_application_vw_&current_set. as
SELECT
      SNAP_DATE
      ,STRM
      ,ADMIT_TERM
      ,EMPLID
      ,last_name
      ,first_name
      ,middle_name
      ,name_suffix
      ,name_display
      ,sex
      ,birthdate
      ,age

FROM adm.UGRD_application_vw
where strm in ("&max_of_STRM.")

;
create index emplid on UGRD_application_vw_&current_set.;
create index SNAP_DATE_STRM_admit_type_emplid on UGRD_application_vw_&current_set.(SNAP_DATE, STRM, admit_type, emplid);

quit;

proc contents data=UGRD_application_vw_&current_set.;
run;

data data.VA_DATA_&current_set.;
set work.UGRD_application_vw_&current_set.;
run;

/* and sorry I did not make a data set, I was just thinking maybe I left an option out and it was in the the contents of work, I see a folder there but it is not an index file */

Re: Index files?

kiranv_ — Wed, 14 Feb 2018 00:14:05 GMT

below is pasted from SAS documentation,

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000279149.htm

which will explain whether indexes are used by your query or not. below is some notes from the link above

if MSGLEVEL=I, SAS writes informative messages to the SAS log about index processing. In general, when a WHERE expression is executed for a data set with indexes, the following information appears in the SAS log:

if an index is used, a message displays that specifies the name of the index
if an index is not used but one exists that could optimize at least one condition in the WHERE expression, messages provide suggestions that describe what you can do to influence SAS to use the index. For example, a message could suggest sorting the data set into index order or to specify more buffers.

please also look into below paper, which explain under what conditions indexes are useful. data access through is effcient many times but not all the times and hence some queries do not use indexes.

read below paper which explains details about sas indexes and their usage

http://www2.sas.com/proceedings/sugi30/247-30.pdf

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000279149.htm

Re: Index files?

kjohnsonm — Wed, 14 Feb 2018 00:14:19 GMT

I did not know this "which creates a new data set without an index."

can you share a sample

Re: Index files?

ballardw — Wed, 14 Feb 2018 00:45:37 GMT

@kjohnsonm wrote:

I did not know this "which creates a new data set without an index."

can you share a sample

data work.class(index=(name));
   set sashelp.class;
run;

proc contents data=work.class;
run;

data work.class2;
   set work.class;
run;

proc contents data=work.class2;
run;

The first data set made, work.class explicitly has an index.

But when I run the second data step creating work.class2 there is no index specified.

Without that instruction work.class2 will not have an index.

In your code:

data data.VA_DATA_&current_set.; <= no index statement so this NEW data set does not have an index.
set work.UGRD_application_vw_&current_set.; <= the data set referenced here had the index created
run;

Either add the INDEX option on the new set or use Proc Datasets with the Copy instruction to copy the data set and use the options to include the index.

Re: Index files?

kjohnsonm — Wed, 14 Feb 2018 01:00:06 GMT

I had seen this style of code but falsely assumed it was just an variation on a way to write indexes. That is very big wrong in this case. thanks -KJ