Validate multipe tablename and Error handling using sas macro

[Tom]

If you want to test if a string is a valid SAS name then just use the NVALID() function.  If you want to it in macro code then use the %SYSFUNC() macro function to call the NVALID() function.

If you want to test whether DATA is valid as SAS names then do not first convert the DATA into macro variables.  Test the DATA in a SAS code.  Then if there is some reason to convert some of the data into macro variables you can do that later.

[Patrick]

@Tom Ah, yes, nvalid()

Forgot the function name and when I searched through the docu got confused with notname() which doesn't quite cut it which is why I've used a RegEx. 

[Kurt_Bremser]

The first possible issue I see is here:

data mapping;
set mapping;
if target_column='bank_key'
then mapping='0'; /* if variable mapping is not already contained in dataset mapping,
it will be defined as character with a length of 1, leading to truncation later */
if target_column='change_begin_date' then mapping='%SYSFUNC(DATETIME())';
if target_column='change_end_date' then mapping="'01JAN5999:00:00:00'DT";
if target_column='current_ind' then mapping='"Y"';
if target_column = "bank_status_desc" then delete;
run;

This

proc sql;
    select distinct source_table into:source_table
    from mapping
    where source_table is not null;
run;

will not create a list, only one value will make it into the macro variable; use the SEPARATED BY option to get a list of values.

SQL statements are executed immediately, so no RUN is needed. Properly terminate the SQL procedure with a QUIT statement.

This can never work:

and %substr(&source_table., &i, 1) ne "$/,!@#%^&*" 

You compare a single character with a string; on top of that, you make the mistake of using quotes in macro language. Since the macro language only has the datatype text, quotes are not needed, they actually become part of the text.

It will be easier for you to do the check in a DATA _NULL_ step instead of a macro.

These are just some observations I've made in your code, there's more. Get your code to work, step-by-step, and always take a look at the results before proceeding to the next step.

[varshabansal]

can you suggest me how to check for list of variables in sas code?

[Kurt_Bremser]

In PROC SQL, query DICTIONARY.COLUMNS. In a DATA step, use SASHELP.VCOLUMN.