01-23-2014 03:39 PM
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 !!!
01-23-2014 03:51 PM
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?
01-23-2014 04:58 PM
Here is some code that looks to work OK if you want to do it yourself.
01-23-2014 04:02 PM
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.
Need further help from the community? Please ask a new question.