topic Re: check if index exists? in ODS and Base Reporting

check if index exists?

deleted_user — Fri, 15 Dec 2006 16:33:38 GMT

Is there a way to check if an index already exists on a dataset?

Re: check if index exists?

TobyDunn_hotmail_com — Fri, 15 Dec 2006 21:56:35 GMT

Yeap sure can.... I am going to show you one of many ways to get this information. There are others however it will depend on how you wish to use this.

Data One ( Index = ( x ) ) ;
Do X = 1 To 10 ;
Y = 'A' ;
Output ;
End ;
Run ;

Proc SQL ;
Select Case When Max( IDXUsage ) Ne '' Then 'Y'
Else 'N' End Into: IDXCheck
From Dictionary.Columns
Where MemName = 'ONE' ;
Quit ;

%Put >>&IDXCHeck<< ;

Re: check if index exists?

Olivier — Mon, 18 Dec 2006 18:22:40 GMT

The ATTRN function offers a "ISINDEX" attributes that solves your problem...

DATA _NULL_ ;
dsid = OPEN(yourDataSet) ;
isIndexed = ATTRN(dsid,"ISINDEX") ;
PUT isIndexed = ;
RUN ;

The useful feature of this is that you can use it through the %SYSFUNC macro facility :

%SYSFUNC(ATTRN(%SYSFUNC(OPEN(yourDataSet)),ISINDEX))

In both cases, you get a 0 or a 1 depending on whether the table has an index (1) or not (0).

Re: check if index exists?

deleted_user — Tue, 19 Dec 2006 18:35:30 GMT

It works! Thanks for your suggestions. I wanted to check if any index already existed on the dataset. If it did not then create one. I modified the code as below:

%macro flg_exist;
%let dsid=%sysfunc(open(dataset_name));
%let ndx_flag=%sysfunc(attrn(&dsid,isindex));
%put Index exists flag= &ndx_flag;

%let rc=%sysfunc(close( &dsid ));
%put Dataset closed return code= &rc;

%if &ndx_flag=1 %then %let ndx_delete=index delete zip5 %str(;);
%else %if &ndx_flag=0 %then %let ndx_delete=;
%put Index delete statement =&ndx_delete;
%mend;

%flg_exist;

proc datasets lib=etldir nolist;
modify dataset_name;
&ndx_delete;
index create zip5;
quit;
Message was edited by: sk at Dec 19, 2006 1:36 PM