@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(¤t_distinct_var_list));
%let var=%scan(¤t_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.
... View more
