topic Re: How to make imported SAS variable name conform to convention in SAS Programming

How to make imported SAS variable name conform to convention

caveman529 — Thu, 23 Jan 2014 20:39:00 GMT

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 !!!

Re: How to make imported SAS variable name conform to convention

Murray_Court — Thu, 23 Jan 2014 20:51:28 GMT

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?

Re: How to make imported SAS variable name conform to convention

data_null__ — Thu, 23 Jan 2014 20:56:47 GMT

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

Re: How to make imported SAS variable name conform to convention

Reeza — Thu, 23 Jan 2014 21:02:26 GMT

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.

Re: How to make imported SAS variable name conform to convention

caveman529 — Thu, 23 Jan 2014 21:39:47 GMT

it worked!!!! Thank you so much!

Re: How to make imported SAS variable name conform to convention

data_null__ — Thu, 23 Jan 2014 21:58:41 GMT

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;