DATA Step, Macro, Functions and more

How to keep an index in a dataset

Accepted Solution Solved
Reply
Occasional Contributor Rah
Occasional Contributor
Posts: 5
Accepted Solution

How to keep an index in a dataset

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!


Accepted Solutions
Solution
‎02-11-2012 11:08 PM
SAS Employee
Posts: 104

Re: How to keep an index in a dataset

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)
 

View solution in original post


All Replies
Super User
Super User
Posts: 7,076

How to keep an index in a dataset

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 ∈

....

%mend;

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


Regular Contributor
Posts: 184

How to keep an index in a dataset

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.

Solution
‎02-11-2012 11:08 PM
SAS Employee
Posts: 104

Re: How to keep an index in a dataset

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)
 
Occasional Contributor Rah
Occasional Contributor
Posts: 5

How to keep an index in a dataset

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!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 999 views
  • 3 likes
  • 4 in conversation