How to check that columns name and observations will contain alphabets ,number and underscore?

[varshabansal]

Hello everyone!

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

Thanks in advance

[LinusH]

You can use NVALID function, applied to queries towards dictionary.tables and dictionary.columns (in PROC SQL).

https://documentation.sas.com/doc/en/pgmsascdc/v_044/lefunctionsref/p19fp3i1f1hkomn12366grwnl8vt.htm

[Kurt_Bremser]

Run your SAS system with

options validvarname=v7 validmemname=compatible;

and such a test is unnecessary.

[varshabansal]

Thanks, but i want code for that because if my condition satisified it is ok but if not matching the condition i want to abort my job and work on error.

[Kurt_Bremser]

For already existing datasets, use the DICTIONARY tables and the NVALID function.

Newly created datasets cannot be an issue if the import job is properly done (no Excel, no PROC IMPORT).

[Amir]

Hi,

Please share anything you have already tried with the log showing the code and any issues by posting it via the Insert Code icon "</>".

Otherwise, try looking into the below approaches, also see Accessing SAS Information By Using DICTIONARY Tables. One function to help examine the characters is the compress()  function with the use of the 'n' modifier to remove "digits, the underscore character, and English letters to the list of characters", so if your result is an empty string then you'll know there were no other characters.

1. For data set names use SQL to search dictionary.tables.
2. For column names use SQL to search dictionary.columns.
3. For observations use SQL or a data step to examine the observations.

Come back if you have any questions, showing what you have tried.

Thanks & kind regards,

Amir.

[Tom]

If you are testing for SAS names use the NVALID function with V7 option.  That will check if the name is 32 characters are less and follows SAS's naming rules.

%macro test(name);
%sysfunc(nvalid(&name,v7))
%mend test;

If the name is intended for something that accepts names longer then 32 bytes you could use FINDC() function instead and add your own length limit.

%macro test(name);
%length(&name) and (%length(&name) <= 128) and (not %sysfunc(findc(&name,_,nk)))
%mend test;

[Tom]

For a SAS macro that extends the functionality of NVALID() to other types of SAS names you could use this one:

https://github.com/sasutils/macros/blob/master/nvalid.sas

A function style macro that extends the NVALID() SAS function.

In addition to the name types supported by NVALID() it adds support for
  - V6 names (length <=8)
  - FORMAT/INFORMAT names
  - MEMBER names.

Meaning of TYPE value choice:
ANY - Any string from 1 to 32 characters long.
V7 - Only contains Letters, digits or _ and does not start with digit.
UPCASE - Same as V7 but lowercase letters not allowed.
V6 - Same as V7 but max length is 8 instead of 32.
NLITERAL - A name that is not V7 valid must be in NLITERAL form.
FILEREF - Same as V6 with additional test if FILEREF is defined
LIBREF  - Same as V6 with additional test if LIBREF is defined
FORMAT  - Same as V7 but allow $ prefix and exclude terminal digit
INFORMAT  - Same as V7 but allow $ prefix and exclude terminal digit
MEMNAME - Valid membername based on VALIDMEMNAME setting.
COMPAT - an alias for COMPATIBLE.
COMPATIBLE - Same as V7 but used for member name.
EXTEND - Same as NLITERAL except there are extra excluded characters.

[Ksharp]

/*Also could check function NOTNAME()*/
data have;
input x $;
notname=notname(strip(x));
cards;
12ds
sw34
_1d3
d.3f
;