How to dynamically create the index?

[David_Billa]

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 it 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 it. 

[Patrick]

Is your intention to create an index per variable in the list or to create a single composite index with all the variables in the list.

If composite index:

- Create some sample code for such a composite index to test the syntax

- Populate your macro variable in a way so the string matches the required syntax for a composite index

[David_Billa]

Want to create Index per variable in the list

[Patrick]

---

@David_Billa wrote:
Want to create Index per variable in the list

---

Then what @s_lassen already proposed should do the job.

[s_lassen]

You can do it with a macro, e.g.:

%macro create_indexes(table,fields);                                                                                                    
  %local i var;                                                                                                                         
  Proc SQL;                                                                                                                             
  %do i=1 %to %sysfunc(countw(&fields));                                                                                                
    %let var=%scan(&fields,&i);                                                                                                         
    create index &var on &table(&var);;                                                                                                 
    %end;                                                                                                                               
  quit;                                                                                                                                 
%mend;       
options mprint;                                                                                                                         
%let s=a,v,g;                                                                                                                           
%create_indexes(a,%quote(&s));     

If you use the originale variabl, &CURRENT_TYP, I think the macro call will work just as well (the PRXCHANGE call just changed "*" to "," in the string), and you will not have to %QUOTE in when calling the macro.

[David_Billa]

@s_lassen I could not understand the below part.

If you use the originale variable, &CURRENT_TYP, I think the macro call will work just as well (the PRXCHANGE call just changed "*" to "," in the string), and you will not have to %QUOTE in when calling the macro.

[Tom]

Don't use SQL and the syntax is much simpler. Use PROC DATASETS instead.

proc datasets nolist lib=work;
modify sas_prompts_lib_table;
  create index division product_family upn ;
run;
quit;

Which mean you can pass the list of variables easily in a macro variable.

%let varlist= division product_family upn;
...
proc datasets nolist lib=work;
modify sas_prompts_lib_table;
  create index &varlist;
run;
quit;

[David_Billa]

@Tom Thanks for your suggestion. How to tweak your code if the table name and fields (varlist) are dynamic? 

[Tom]

---

@David_Billa wrote:

@Tom Thanks for your suggestion. How to tweak your code if the table name and fields (varlist) are dynamic? 

---

You need to provide more details to get a detailed response.

In general if you have the data in a dataset then use a data step to write the code.

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;

[David_Billa]

@Tom Thanks again for the instructions. When I tried your code with Sashelp.class instead of index_list table and made (tried myself) the required changes to make it work to get idea of your solution. Unfortunately it's not working. May be I'm missing something.

[Tom · Accepted solution]

Works fine for me.  You need to make the metadata first. 

For example if you wanted it to create an index for every variable (WHY????) you could do this.

2825  data class;
2826    set sashelp.class;
2827  run;

NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


2828
2829  proc contents data=class noprint
2830    out=index_list(keep=libname memname name);
2831  run;

NOTE: The data set WORK.INDEX_LIST has 5 observations and 3 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


2832
2833  filename code temp;
2834  data _null_;
2835    set index_list;
2836    by libname memname ;
2837    file code;
2838    if first.libname then put 'proc datasets nolist lib=' libname ';' ;
2839    if first.memname then put 'modify ' memname ';' / '  create index ' @;
2840    put name @;
2841    if last.memname then put ';' / 'run;' ;
2842    if last.libname then put 'quit;' ;
2843  run;

NOTE: The file CODE is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 5 records were written to the file (system-specific pathname).
      The minimum record length was 4.
      The maximum record length was 43.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


2844  %include code / source2;
NOTE: %INCLUDE (level 1) file CODE is (system-specific pathname).
2845 +proc datasets nolist lib=WORK ;
2846 +modify CLASS ;
NOTE: Simple index Age has been defined.
NOTE: Simple index Height has been defined.
NOTE: Simple index Name has been defined.
NOTE: Simple index Sex has been defined.
NOTE: Simple index Weight has been defined.
2847 +  create index Age Height Name Sex Weight ;
2848 +run;

NOTE: MODIFY was successful for WORK.CLASS.DATA.
2849 +quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


NOTE: %INCLUDE (level 1) ending.

[David_Billa]

Prefect! I missed the meta data portion. Do you have other method to accomplish the same without using proc datasets and macros? Reason is our project not used macros for creating the index and I don't want to create nested macro for this solution.

Project leader advised not to use proc datasets till SAS Admin confirms.

I'm unable to think of other solution without proc datasets and macros.

[Tom]

---

@David_Billa wrote:
Prefect! I missed the meta data portion. Do you have other method to accomplish the same without using proc datasets and macros? Reason is our project not used macros for creating the index and I don't want to create nested macro for this solution.

Project leader advised not to use proc datasets till SAS Admin confirms.

I'm unable to think of other solution without proc datasets and macros.

---

The code I posted is not using macro code at all.

You can easily convert the code generating data step to generate PROC SQL code instead. 

But the restriction to use PROC SQL instead of PROC DATASETS is silly.

You could convert the code generation step to use CALL EXECUTE() instead of writing to a file. But the log will be uglier and the data step will be harder to debug.

[David_Billa]

I have not said that your solution has macro but other solution had.

Anyway to convert your solution to proc sql without call execute? Sorry for too many restrictions.

[Tom]

---

@David_Billa wrote:
I have not said that your solution has macro but other solution had.

Anyway to convert your solution to proc sql without call execute? Sorry for too many restrictions.

---

You have now lost me. Are you asking how to modify the data step to write PROC SQL code instead of PROC DATASETS code?

You can do it yourself.  Try it.  If it does not work then post what you tried are we can see if we see what needs to change.