How to dynamically create the index?

[David_Billa]

Yes, I'm asking how to convert to proc sql.

I will give a try as well.

[Tom]

---

@David_Billa wrote:
Yes, I'm asking how to convert to proc sql.

I will give a try as well.

---

Go for it.

Hint: The tricky part is getting the libname and memname written with just a period between them.

[David_Billa]

@Tom  Your code is perfectly fine but it's complex for our project leader and also I was asked to create the index within proc only. Hence I tried the %do block which you see at last step but it's not working. 

%let nobs=0;
select nobs format=32. into :nobs trimmed
  from connection to database(
    select count(*) as nobs from &schema_temp..&prompt_tbl_name 
  )
;

%if &nobs %then %do;
      
      create table &prompt_tbl_name as
        select * from connection to _snow_temp (
          select *
          from &schema_temp..&prompt_tbl_name;
        )
      ;
%end;

%do i=1 %to %sysfunc(countw(&current_distinct_var_list));                                                                                                
    %let var=%scan(&current_distinct_var_list,&i);                                                                                                         
    create index &var on &prompt_tbl_name(&var);;                                                                                                 
%end; 

Error message;

SYMBOLGEN:  Macro variable CURRENT_DISTINCT_VAR_LIST resolves to bsc_division,ce_to_sud_mapping,product_family
WARNING: In a call to the COUNTW function or routine, the modifier "R" not valid.
WARNING: In a call to the COUNTW function or routine, the modifier "_" not valid.
WARNING: In a call to the COUNTW function or routine, the modifier "Y" not valid.
MLOGIC(TEST):  %DO loop beginning; index variable I; start value is 1; stop value is 13; by value is 1.  
MLOGIC(TEST):  %LET (variable name is VAR)
SYMBOLGEN:  Macro variable CURRENT_DISTINCT_VAR_LIST resolves to division,mapping,product
SYMBOLGEN:  Macro variable I resolves to 1
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       ce_to_sud_mapping 
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR: The macro TEST will stop executing.