SAS Safe Column Names

Question

Is there an easy way in SAS to convert a string to a safe SAS name that will be used as the column name?

t

Rob Penridge ----> Rob_Penridge $*@' Blah@ * ----> ____Blah__ 

I use proc transpose and then want to work with renamed columns after transposition.

Answer 1

proc transpose will accept these names without any changes if you set options validvarname=any;

If you want to work with columns afterwards, you can use the NLITERAL function to create name literals that can be used to reference them:

 options validvarname=any; /* Create dataset and transpose it */ data zz; var1 = "Rob Penridge"; var2 = 5; output; var1 = "$*@' Blah@ *"; var2 = 100; output; run; proc transpose data = zz out = zz_t; id var1; run; /* Refer to the transposed columns in the dataset using NLITERAL */ data _null_; set zz; call symput(cats("name", _n_), nliteral(var1)); run; data blah; set zz_t; &name1. = &name1. + 5; &name2. = &name2. + 200; run; 

Answer 2

May try perl regex function. Since the first character does not have to be numeric for the column name, it is more complex.

 data _null_; name1 = "1$*@' Blah1@ *"; name2 = prxchange("s/[^A-Za-z_]/_/",1,prxchange("s/[^A-Za-z_0-9]/_/",-1,name1)); put name2; run; 

Answer 3

Take a look at VALIDVARNAME . This may allow you to accept invalid SAS names.

The NOTNAME function can also make it easier to find invalid characters.

Answer 4

What about using the SAS regular expression function? For instance:

 data names; set name; name_cleaned = prxchange('s/[^a-z0-9 ]/_/i', -1, name); run; 

This converts everything that is not a letter, number, or space into _ . You can add other characters that you want to allow to the list after 9 . Just keep in mind that some characters are "special" and must be preceded by \ .

Answer 5

You can also use the IDLABEL operator in a transposition to add labels corresponding to the original values. Then use the VARLABEL function to get tags and work with them that way.