How to make imported SAS variable name conform to convention

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

How to make imported SAS variable name conform to convention

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


Accepted Solutions
Solution
‎01-23-2014 03:56 PM
Respected Advisor
Posts: 3,799

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

Posted in reply to caveman529

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

View solution in original post


All Replies
Contributor
Posts: 45

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

Posted in reply to caveman529

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?

Solution
‎01-23-2014 03:56 PM
Respected Advisor
Posts: 3,799

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

Posted in reply to caveman529

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

Regular Contributor
Posts: 161

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

Posted in reply to data_null__

it worked!!!!  Thank you so much!

Respected Advisor
Posts: 3,799

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

Posted in reply to caveman529

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;
Super User
Posts: 19,870

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

Posted in reply to caveman529

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

hospital type: local = hospital_type__local

Jun-30-2005 = June_30_2005

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2473 views
  • 3 likes
  • 4 in conversation