I have the macro program as below. Now I want to write a code between execute statement and create table Libname.&prompt_tble_name to check the number of observation in &schema_temp..&prompt_tbl_name and if it's greater than 0 then only execute next step otherwise just put a note in the log. I was asked not to create separate macro for this.   %macro test; /* macro variable definitions code will be here and then below code*/ execute( create or replace table &schema_temp..&prompt_tbl_name as select distinct &current_distinct_var_list from &schema_temp..&source_tbl ; ) by database create table Libname.&prompt_tble_name as select * from connection to database( select * from &schema_temp..&prompt_tbl_name; ) ; %mend; %test; I can test the number of observations using below method but I'm not certain how to use the &totobs to execute the next step which is  'create table libname.&prompt_tble_name' data _NULL_; if 0 then set &schema_temp..&prompt_tbl_name nobs=n; call symputx('totobs',n); stop; run; %put no. of observations = &totobs; ... View more

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?   ... View more

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.    ... View more

Is there any better method other than shown below 'select distinct' method to create table in database from SAS? I tried proc summary but it's  failing on some of the larger tables   %macro test_loop( type_list = ); proc sql noprint; connect using &db_lib as _snow_temp; execute( create or replace table &schema_temp..&prompt_tbl_name as select distinct &current_distinct_var_list from &schema_temp..&source_tbl ; ) by _snow_temp; create table DATA.&prompt_table_name as select * from connection to _snow_temp ( select * from &schema_temp..&prompt_tbl_name; ) ; %end; quit; %mend test_loop; %let type_list = product_family*upn product_family*region plant_id*batch batch*region batch*event_region ; %test_loop( type_list = &type_list ) ... View more