How to check that columns name and observations in a table will contain aplhabets ,number and underscore?

Question

I want to create a macro that will check that table_name(dataset name), column_names and their observations will contain alphabets,number and underscore, if not (else) throw an error.

Thanks in advance

Answer 1

Let's break this up into the general logic that we need to complete the task. First, we want to know if any of your criteria only includes alphabets, number, and underscore. We can do this using the compress function with the N modifier, which only includes digits, the underscore character, and English letters. Compress will remove all of these characters. The logic we'll use:

• If we use compress(string,,'N') on some string and its result is not missing, throw an error

Now we need to run this logic on:

1. A table name
2. Its column names
3. The values of each column

Full code:

%macro check_table(lib=, dsn=);

    /* Check table name */
    %if(%sysfunc(compress(%bquote(&dsn),,N)) NE) %then %do;
        %put ERROR: &lib..&dsn. has invalid characters;
        %abort;
    %end;

    /* Check if table exists */
    %if(NOT %sysfunc(exist(&lib..&dsn.))) %then %do;
        %put ERROR: &lib..&dsn. does not exist;
        %abort;
    %end;

    /* Check column names */
    proc sql noprint;
        select *
        from dictionary.columns
        where     libname=upcase("&lib")
              AND memname=upcase("&dsn")
              AND compress(name,,'N') NE ''
        ;
    quit;

    %if(&sqlobs > 0) %then %do;
        %put ERROR: &lib..&dsn. has column names with invalid characters;
        %abort;
    %end;

    /* Check column values */
    data _null_;
        set &lib..&dsn.;
        array charvars[*] _CHARACTER_;

        do i = 1 to dim(charvars);
            if(compress(charvars[i],,'N')) NE '' then do;
                put "ERROR: &lib..&dsn. has column values with invalid characters";
                abort;
            end;
        end;
    run;

    %put NOTE: &lib..&dsn. has a valid table name, valid column names, and valid column values.;
%mend;

Example:

%check_table(lib=sashelp, dsn=cars);

ERROR: sashelp.cars has column values with invalid characters
ERROR: Execution terminated by an ABORT statement at line 34 column 82.
Make=Acura Model=3.5 RL 4dr Type=Sedan Origin=Asia DriveTrain=Front MSRP=$43,755 Invoice=$39,014 EngineSize=3.5 Cylinders=6
Horsepower=225 MPG_City=18 MPG_Highway=24 Weight=3880 Wheelbase=115 Length=197 i=2 _ERROR_=1 _N_=5
NOTE: The SAS System stopped processing this step because of errors.