topic How to keep an index in a dataset in SAS Programming

How to keep an index in a dataset

Rah — Sat, 11 Feb 2012 15:24:32 GMT

Hello!

I have a macro that receives a dataset by parameter. This dataset may contain and index, built like:

DATA TEST (index=(IDXPR = (USER_ID USER_TYPE REGISTRATION_DATE) /UNIQUE /NOMISS));

The macro code uses a DATA TEST; SET TEST; step to update the table. Therefore, it erases the index.

Is there a way to keep the index with the same structure as it had before the SET TEST; ?

I tried to search about rebuild, but from what I found, it looked like the command is used just to repair indexes.

I also tried the PROC CONTENTS, but I did not find the UNIQUE and NOMISS options there.

Thanks in advance!

How to keep an index in a dataset

Tom — Sat, 11 Feb 2012 17:06:46 GMT

I am reminded of the old joke:

PATIENT: Doctor, it hurts when I do this.

DOCTOR: Don't do that.

Seriously look into using the UPDATE statement of PROC SQL. Or if you are just adding observations you could use PROC APPEND.

Otherwise force the macro's user to provide you with the necessary dataset options to have the dataset re-created with the options that they want.

%macor myupdate(in=,out=);

data &out;

set &in;

....

%mend;

%myupdate(in=test,out=TEST (index=(IDXPR = (USER_ID USER_TYPE REGISTRATION_DATE) /UNIQUE /NOMISS)));

How to keep an index in a dataset

Howles — Sun, 12 Feb 2012 03:23:03 GMT

A DATA step with a MODIFY statement would also preserve the index.

Rah should understand that the DATA TEST; SET TEST; construct does not change an existing table. Rather it creates a new table which replaces the pre-existing one.

Re: How to keep an index in a dataset

SASJedi — Sun, 12 Feb 2012 04:08:01 GMT

If you MUST rewrite the data set to update it, this concept macro might help:

 
%macro Update(master,update,by);
/*This macro will update data in the master data set, preserving indexes*/
%let master=%qupcase(&master);
%let update=%qupcase(&update);
%let memname=%qscan(&master,2,.);

%if &memname= %then %do;
   %let libname=WORK;
   %let memname=&master;
%end;
%else %do;
   %let libname=%qscan(&master,1,.);
%end;

proc sort data=sashelp.VINDEX (where=(libname="&libname" and MEMNAME="&memname"))
          out=indexes;
   by INDXNAME INDXPOS;
run;

data _null_;
   length Indx Names $2000;
   retain indx names;
   set indexes nobs=nobs end=last;
   by INDXNAME;
   if nobs=0 then do;
      call symputx ('index',' ');
      stop;
   end;
   IF _n_=1 then do;
      indx="(INDEX=(";
   end;
   if first.INDXNAME then do;
       Names='';
        indx=catx(' ',indx,indxname,'=(');
   end;
   Names=catx(' ',Names,Name);
   if last.INDXNAME then do;
        indx=catx(' ',indx,names,')');
   end;
   if last then do;
   call symputx ('index',CATX(' ',indx,'))'));
   end;
run;

data &master &Index;
   update &master &update;
   by &by;
run;
%mend;

/*Make a dataset with an index - we will update this one*/
Data TEST (index=(Name=(Name) Comp=(Age Height)));
  set sashelp.class;
run;

/*Make a dataset with the update values*/
data UpDates;
  set sashelp.class (obs=2 keep= Name Age Height);
  Age=21;
  Height=70;
run;

/*Use the update macro to update the dataset and preserve the indexes*/
options mprint;
%Update(work.test,work.updates, Name)

How to keep an index in a dataset

Rah — Wed, 04 Apr 2012 12:29:45 GMT

Worked perfectly! I just added two more lines into your concept macro, and would like to share it:

In the part:

if last.INDXNAME then do;

indx=catx(' ',indx,names,')');

end;

I've added:

if last.INDXNAME then do;

indx=catx(' ',indx,names,')');

if unique = 'yes' then indx=catx(' ',indx,'/UNIQUE');

if nomiss = 'yes' then indx=catx(' ',indx,'/NOMISS');

end;

So it's also possible to keep the unique and nomiss.

Thanks again for the help and the patience in writing the code!