How to make imported SAS variable name conform to convention

[caveman529]

Dear All:

I imported an Excel to SAS using proc import.  Unfortunately, the first row (thus the variable names) of the Excel files contain special characters not allowable under SAS variable naming convention.  For example, I have 'hospital type: local' and "Jun-30-2005".  I'm wondering if there is a way to convert all these space, special characters into underscore? 

I know this can easily done in SAS EG, but hope to know how to do it programmically.  Thanks !!!

[Murray_Court]

Its very messy, but here is what I would try:

1. Proc import with obs = 1 without retrieving the names, making a small dataset with just the names.

2. Act upon the dataset with a macro that inspects each character of each title individually and switches it if it doesn't conform (regular expressions would be go for this)

3. Select our names into a macro variable using proc sql

4. proc import with startobs = 2 to pull just the data from the file.

5. use a small macro that renames the automatically generated names with our formatted names stored in our macro variable from step 3.

Anyone have any better ideas?

[data_null__ · Accepted solution]

What happens if you set OPTIONS VALIDVARNAME=V7; and then import?

[caveman529]

it worked!!!!  Thank you so much!

[data_null__]

Here is some code that looks to work OK if you want to do it yourself.

data tov7;
   input name $char32.;
   v7name = name;
   nl = length(v7name);
   if notfirst(first(v7name)) then do;
      if length(v7name) lt 32
         then v7name = '_'||v7name;
         else substr(v7name,1,1)='_'; 
      end;
   do until(f eq 0);
      f = findc(v7name,,'NKT');
      if f then substr(v7name,f,1)='_';
      end;
   do until(l = k);
      l = length(v7name);
      v7name = transtrn(v7name,'__','_');
      k = length(v7name);
      end;
   cards;
hospital type: local
Jun-30-2005
100
  blank first.
  lots     of blanks
12345678901234567890123456789012
;;;;
   run;
proc print;
   run;

[Reeza]

SAS usually does convert them in import. You just don't get nice variable names

hospital type: local = hospital_type__local

Jun-30-2005 = June_30_2005

The labels will still be the names from the excel file.