Validate multipe tablename and Error handling using sas macro

[varshabansal]

I have a main code file and macro code file and multiple tables and I want to validate the table _name and the same for column names, should be alphabets and underscore only by using macro and with do loop and I want that when I call my macro in the main file, if error occurs it generate error code and return code to macro code.

Main code-

/*******import mapping excel file********/
 proc import datafile="/home/viadmin/casuser/mapping.xlsx"
 out= work.mapping
 dbms=xlsx
 replace;
 sheet=sheet1;
 run;

/* update bank_key value */
data mapping;
set mapping;
if target_column='bank_key'
then mapping='0';
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;
/**create list of source and target tables to be loaded***/ 

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

proc sql;
    select distinct target_table, mapping, target_column,count(*) 
        into :target_table,
        :mapping_func separated by ' ' ,
        :trgt_col separated by ' ',
        :count
    from mapping;
run;

%check_table;

%createTable;

proc sql ;
      select compress(put(max(BANK_KEY), best32.))
         into :etls_maxkey
        from amlcore.&target_table;
      quit;

/* %scdType2Loader; */

Macro code

%macro check_table;
  /* Check if the table name is empty. */
  %if (&source_table.="" ) %then 
    %do;
        %put "Error: Table name cannot be empty.";
        %return;
    %end;

  /* Check if the table name contains any special characters. */
  %let i = 1;
  %let l=%length(&source_table.);

  %do i=1 %to &l.;
    /* Check if the current character is a letter or underscore. */
    %if %substr(&source_table., &i, 1) ne " " 
    and %substr(&source_table., &i, 1) ne "_" 
    and %substr(&source_table., &i, 1) ne "." 
    and  %substr(&source_table., &i, 1) ne "0-9 "
    and %substr(&source_table., &i, 1) ne "$/,!@#%^&*" 
    %then %do;
      %put "Error: Table name can only contain letters and underscores.";
      %return;
    %end;
    %let i = &i + 1;
  %end;
   
  /* The table name is valid. */
  %put "Table name is valid.";
%mend check_table;

 %check_table;

Error: also shows error that table name contains underscore and alphabets

Error: error in %do I loop

[PaigeMiller]

What does "validate" mean in this context?

Why are macros and do-loops required? Why couldn't another solution be used here?

What is wrong with the code you show?

Please, some of us refuse to download attachments from internet web sites. Please include your code as text by copying the text and pasting it into the "code box" that appears when you click on the little running man icon.

[varshabansal]

Validate means- i want to check that table name contains only alphabets and
underscore

Macros and do loop are included because i have multiple table name. So for
all table name i want to check that condition rule of alphabet and
underscore.

[PaigeMiller]

---

@varshabansal wrote:
Validate means- i want to check that table name contains only alphabets and
underscore

Macros and do loop are included because i have multiple table name. So for
all table name i want to check that condition rule of alphabet and
underscore.

---

What table names are you talking about? Do you mean SAS data set names? Or some other type of table names? Please be specific.

Macros and do loop do not seem to be necessary, why do you insist on macros and loops? If there is a solution without macros and without do loops, we will likely provide that.

[varshabansal]

I have an excel file which contains different headers for table name, column name like these and there are multiple table name or you can say dataset name so i want to check for that and dataset contains different columnname also .

[PaigeMiller]

Please import the Excel file into a SAS data set, and then a portion of that SAS data set with as working SAS data step code (examples and instructions) and then share that code with us. We cannot work with Excel files.

[Patrick]

Code like below could do or at least hopefully provide you with enough pointers to amend the code to what you need.
- Code amended replacing RegEx with function nvalid() as proposed by Tom.

data work.'class.2'n;
  set sashelp.class;
  length '9new_var'n 8;
  length '$$ $a'n 8;
  length 'a'n 8;
run;

%macro check(lref, tbl);
  data _null_;
    set sashelp.vcolumn(where=(libname=%upcase("&lref") and memname=%upcase("&tbl"))) end=last;

    if _n_=1 then
      do;
        if not nvalid(memname,'v7') then 
          do;
            putlog "Table name " memname "does not conform with SAS naming conventions";
            check_fail_cnt+1;
          end;
      end;
    if not nvalid(name,'v7') then 
      do;
        putlog "Variable name " name "does not conform with SAS naming conventions";
        check_fail_cnt+1;
      end;

    if last then 
      do;
        if check_fail_cnt>0 then
          do;
            /* and here you do whatever you like - abort the job, populate some macro variable etc. */
            putlog "Issues foundin table &lref..&tbl.. Aborting Job!!";
            abort cancel;
          end;
        else
          do;
            putlog "NO issues found in table &lref..&tbl";
          end;
      end;
  run;
%mend;

%check(sashelp, class);
%check(work, class.2);

 And if you've got some driver Excel that you want to use to call the macro then below one way how to do this.

data driver;
  infile datalines truncover dsd;
  input lref:$8. tbl:$32.;
  datalines;
sashelp,class
work,class.2
;

data _null_;
  set driver;
  call execute( cats('%check(',lref,',',tbl,')') );
run;

[varshabansal]

@Patrick memname should only contain alphabets and underscore not any number but here script allows for numbers also.

[Patrick]

---

@varshabansal wrote:
@Patrick memname should only contain alphabets and underscore not any number but here script allows for numbers also.

---

Digits are valid in SAS tables names - but o.k. if you only want to allow letters and the underscore then it's back to a regular expression. 

%macro check(lref, tbl);
  data _null_;
    set sashelp.vcolumn(where=(libname=%upcase("&lref") and memname=%upcase("&tbl"))) end=last;

    if _n_=1 then
      do;
        if not prxmatch('/^[_[:alpha:]]{1,32} *$/',memname) then 
          do;
            putlog "Table name " memname "does not conform with SAS naming conventions";
            check_fail_cnt+1;
          end;
      end;
    if not nvalid(name,'v7') then 
      do;
        putlog "Variable name " name "does not conform with SAS naming conventions";
        check_fail_cnt+1;
      end;

    if last then 
      do;
        if check_fail_cnt>0 then
          do;
            /* and here you do whatever you like - abort the job, populate some macro variable etc. */
            putlog "Issues foundin table &lref..&tbl.. Aborting Job!!";
            abort cancel;
          end;
        else
          do;
            putlog "NO issues found in table &lref..&tbl";
          end;
      end;
  run;
%mend;

[Tom]

---

@varshabansal wrote:
@Patrick memname should only contain alphabets and underscore not any number but here script allows for numbers also.

---

Why?  What type of system are these datasets part of that do not allow digits in dataset names?

For such a simple list of characters VERIFY() is a good tool.

[varshabansal]

I have this type of excel

[Patrick]

That looks like a mapping sheet to support design and implementation of DM processes to load AML core tables. Not sure what this has to do with your initial question.

[varshabansal]

Yes, this is mapping sheet, from this I want to load this table and then extract these table name and column name from source and target and then check for naming convention after that we will perform all etl process for scd and so on.

[Patrick]

---

@varshabansal wrote:
Yes, this is mapping sheet, from this I want to load this table and then extract these table name and column name from source and target and then check for naming convention after that we will perform all etl process for scd and so on.

---

The sample code I've provided for the driver table just needs the libref and table name.

You should know the libref (staging, core?) and you've got the table name already in your Excel so shouldn't be hard to create this driver table. ...or you could also amend the macro provided slightly and use the mapping table as input. 

[Tom]

---

@varshabansal wrote:

I have this type of excel

---

So you have the information in DATA already? 

Why are you writing MACRO code to check the data?

Write SAS code instead.