BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rah
Calcite | Level 5 Rah
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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)
 
Check out my Jedi SAS Tricks for SAS Users

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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)));


Howles
Quartz | Level 8

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.

SASJedi
SAS Super FREQ

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)
 
Check out my Jedi SAS Tricks for SAS Users
Rah
Calcite | Level 5 Rah
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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