topic Re: Create index with looping and without macro in SAS Programming

Create index with looping and without macro

David_Billa — Mon, 27 Feb 2023 18:58:10 GMT

Currently I have the code as below to create the index.
 
Proc sql noprint;
CREATE INDEX division on sas_prompts_lib_table (division );
CREATE INDEX product_family on sas_prompts_lib_table (product_family );
CREATE INDEX upn on sas_prompts_lib_table (upn );
quit;
Now I want to dynamically create the index based on the field name from below SAS macro variable.
%let current_distinct_var_list = %sysfunc(prxchange(%bquote(s/\*/,/), -1, &current_typ));
Value of current_distinct_var_list can have any number of fields separated by comma. How to use this macro variable in create index for each field. May be scan function can helps but I'm not sure to implement. I was asked not create any macro for this as this solution will be added to other existing macro.
 
On a related point, I'd like to know if we can create the index on a table (e.g.,  libname.test) and rename the table (e.g. libname.test_1).  Does the index name get updated too?
 

Re: Create index with looping and without macro

PaigeMiller — Mon, 27 Feb 2023 17:57:25 GMT

The primary ways to create looping in SAS are in DATA steps, and via macros. There is no looping in SQL unless you create a macro that works inside of SQL. You can also create indexes in PROC DATASETS, but again, there is no looping without macros that work inside of PROC DATASETS. So ... I think those are your choices.

Re: Create index with looping and without macro

David_Billa — Mon, 27 Feb 2023 18:23:37 GMT

@PaigeMiller well, do you answer to the below question as well?

I'd like to know if we can create the index on a table (e.g.,  libname.test) and rename the table (e.g. libname.test_1).  Does the index name get updated too?

Re: Create index with looping and without macro

PaigeMiller — Mon, 27 Feb 2023 18:27:11 GMT

Typically, my answer to a question that wants to know if something works or not:

Try it yourself and see.

I think that applies here as well.

Re: How to retain the index after renaming the dataset name?

David_Billa — Tue, 28 Feb 2023 06:56:00 GMT

I've tried the following program to create a index and I want to check whether index will be retained if we rename the dataset name which has index.

I observed that index is not being retained if we rename the dataset name. Is there a way to retain the index even if we rename the dataset name?

data cars;
set sashelp.cars;
run;

proc contents data=cars noprint
out=index_list(keep=libname memname name);
run;

filename code temp;
data _null_;
set index_list;
by libname memname ;
file code;
if first.libname then put 'proc datasets nolist lib=' libname ';' ;
if first.memname then put 'modify ' memname ';' / '  create index ' @;
put name @;
if last.memname then put ';' / 'run;' ;
if last.libname then put 'quit;' ;
run;

%include code/ source2;

data cars_new;
set cars;
run;

proc contents data=cars_new;
run;

proc contents data=cars;
run;

Re: How to retain the index after renaming the dataset name?

ballardw — Tue, 28 Feb 2023 12:35:32 GMT

First you have to realize that

data cars_new;
set cars;
run;

Does not "copy" a data set. It builds a completely new data set setting all of its own properties including indexes (none). Second it has to process every single record.

If you want to copy to a Different library (COPY means keep the same name in this case so you can't have two sets with the same name in the same library). Or Proc Copy but again it goes to a new library.

libname Dest "<library location>";
Proc datasets library=work;
   copy out=dest; /*name of destination library*/
    select cars;
run;
quit;

Or create the Cars_new and run the index afterwards.