The logic of import wizard - rename columns to comply with SAS naming conventions

[ayin]

When using EG import wizard, ticking the box "rename columns to comply with SAS naming conventions", SAS will rename all unconventional column names.

I want to apply the same logic to all values in a dataset, because those values will be used as reference for some further operations.

But I'm not sure how EG import wizard renames columns. What is the logic?

I am only able to pick up some:

• replace space with underscore, e.g. 'Col Name' > 'Col_Name';
• then cut it to 32 characters, e.g. 'One_Two_Three_Four_Five_Six_Seven_Nine' > One_Two_Three_Four_Five_Six_Seve';
• if there are same names, then use number to replace the last character, e.g. 'One_Two_Three_Four_Five_Six_Seve
  ' > 'One_Two_Three_Four_Five_Six_Sev0';

However I believe it is not the whole picture, because I've run a sample test:

/* before */
One Two Three Four Five Six Seven Eight
One Two Three Four Five Six Seven Nine
One_Two_Three_Four_Five_Six_Seven_Nine

/* after */
One_Two_Three_Four_Five_Six_Seve
One_Two_Three_Four_Five_Six_0001
One_Two_Three_Four_Five_Six_Sev0

[ayin]

One way to go around the problem is to create a storage (e.g. macro variable) and then store the value, create an empty dataset using this value as the column name, go through SAS EG import wizard and get the final value. Repeat for each value in the dataset. But it might take too much time.

[ballardw · Accepted solution]

Are you implying the data values in one data set are supposed to be the column names in another?

IF that is the case then I wouldn't touch any of this with proc import but would control the variable names with a data step to read the data and then create a custom informat with information from one table to map the values to the desired names.

If this is not the case then maybe you should provide some example data values that need this approach as I am somewhat confused.

Note that the exact same long variable names may get mapped to different values in different proc import results if the order of the variables changes. Also in the case some column headers where the text for the first 40 or so plus characters are the same you will sometimes get a resulting name of VARXXX where the XXX is the column number because the renaming algorithm just gives up.