BookmarkSubscribeRSS Feed
Obsidian | Level 7

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

Tourmaline | Level 20

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

Data never sleeps
Obsidian | Level 7
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.
Super User

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




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,


Super User Tom
Super User

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);
%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;


Super User Tom
Super User

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

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)
  - 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.
Super User
/*Also could check function NOTNAME()*/
data have;
input x $;


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 6 in conversation