SAS Safe Column Names (Updated)

Question

Is there a simple way in SAS to convert a string to a SAS-safe name that would be used as a column name?

ie.

Rob Penridge ---->  Rob_Penridge

$*@'Blah@*   ---->  ____Blah__

I'm using a proc transpose and then want to work with the renamed columns after the transpose.

EDIT: 8 year follow-up... is there now a better way to do this? I feel like I saw a better method sometime back but I'm struggling to find any documentation/examples now that I need to do it.

Answer 1

proc transpose will take those names without any modification, as long as you set options validvarname=any;

If you want to work with the columns afterwards, you can use the NLITERAL function to construct named literals that can be used to refer to 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 regular expression function. Since for column name, the first character should not be numerical, it's more complicated then.

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