BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
caveman529
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

View solution in original post

5 REPLIES 5
Murray_Court
Quartz | Level 8

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__
Jade | Level 19

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

caveman529
Calcite | Level 5

it worked!!!!  Thank you so much!

data_null__
Jade | Level 19

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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