topic Re: Validate multipe tablename and Error handling using sas macro in SAS Programming

Validate multipe tablename and Error handling using sas macro

varshabansal — Thu, 05 Oct 2023 12:04:09 GMT

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

Re: Validate multipe tablename and Error handling using sas macro

PaigeMiller — Thu, 05 Oct 2023 11:42:44 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

varshabansal — Thu, 05 Oct 2023 11:50:54 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

PaigeMiller — Thu, 05 Oct 2023 12:00:06 GMT

@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.

Re: Validate multipe tablename and Error handling using sas macro

varshabansal — Thu, 05 Oct 2023 12:14:49 GMT

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 .

Re: Validate multipe tablename and Error handling using sas macro

PaigeMiller — Thu, 05 Oct 2023 12:24:44 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

Patrick — Thu, 05 Oct 2023 13:29:39 GMT

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;

Re: Validate multipe tablename and Error handling using sas macro

Tom — Thu, 05 Oct 2023 13:18:30 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

Patrick — Thu, 05 Oct 2023 13:25:52 GMT

@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.

Re: Validate multipe tablename and Error handling using sas macro

varshabansal — Fri, 06 Oct 2023 04:46:52 GMT

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

Re: Validate multipe tablename and Error handling using sas macro

Patrick — Fri, 06 Oct 2023 05:31:18 GMT

@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;

Re: Validate multipe tablename and Error handling using sas macro

varshabansal — Fri, 06 Oct 2023 06:16:31 GMT

I have this type of excel

Re: Validate multipe tablename and Error handling using sas macro

Patrick — Fri, 06 Oct 2023 06:23:02 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

varshabansal — Fri, 06 Oct 2023 06:28:17 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

Patrick — Fri, 06 Oct 2023 06:42:32 GMT

@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.

Re: Validate multipe tablename and Error handling using sas macro

Kurt_Bremser — Fri, 06 Oct 2023 07:08:16 GMT

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.

Re: Validate multipe tablename and Error handling using sas macro

varshabansal — Fri, 06 Oct 2023 08:38:35 GMT

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

Re: Validate multipe tablename and Error handling using sas macro

Kurt_Bremser — Fri, 06 Oct 2023 10:52:01 GMT

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

Re: Validate multipe tablename and Error handling using sas macro

Tom — Fri, 06 Oct 2023 12:54:39 GMT

@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.

Re: Validate multipe tablename and Error handling using sas macro

Tom — Fri, 06 Oct 2023 12:58:05 GMT

@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.