how to write DBSASTYPE code for excel column names with symbols

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

how to write DBSASTYPE code for excel column names with symbols

I have multiple excel files where the variable length is different.  Once in SAS I need to combine these files so I need to use libname excel code and read in the variable names using DBSASTYPE.  Two of my variable names in excel contain symbols (+ and .)  Without going into each excel file and changing the column name to exclude the symbol, is there code that will ignore those symbols?

The column in excel is ADD+, how do I code that in the DBSASTYPE lines???

 

LIBNAME WrkBk EXCEL "filepath/file" MIXED=YES;

DATA test1;
SET WrkBk."Subject$"n
(DBSASTYPE=(

"PACKAGE NO"='char(8)'

"SEQ"='char(8)'
"ID"='char(13)'
"NAME"='char(27)'

"ADD+"='char(27)'

));
RUN;
LIBNAME WrkBk CLEAR;


Accepted Solutions
Solution
‎03-22-2018 05:02 PM
Super User
Posts: 3,870

Re: how to write DBSASTYPE code for excel column names with symbols

LIBNAME WrkBk EXCEL "filepath/file" MIXED=YES;
DATA test1;
SET WrkBk."Subject$"n
(DBSASTYPE=(
"PACKAGE NO"n='char(8)'
SEQ='char(8)'
ID='char(13)'
NAME='char(27)'
"ADD+"n='char(27)'
));
RUN;
LIBNAME WrkBk CLEAR;

This is how you reference non-SAS compatible column names. The rest of the code is untested.

View solution in original post


All Replies
Solution
‎03-22-2018 05:02 PM
Super User
Posts: 3,870

Re: how to write DBSASTYPE code for excel column names with symbols

LIBNAME WrkBk EXCEL "filepath/file" MIXED=YES;
DATA test1;
SET WrkBk."Subject$"n
(DBSASTYPE=(
"PACKAGE NO"n='char(8)'
SEQ='char(8)'
ID='char(13)'
NAME='char(27)'
"ADD+"n='char(27)'
));
RUN;
LIBNAME WrkBk CLEAR;

This is how you reference non-SAS compatible column names. The rest of the code is untested.

Occasional Contributor
Posts: 12

Re: how to write DBSASTYPE code for excel column names with symbols

Thanks SASKiwi.  Adding the n seems to get me past the ADD+ but I forgot to include in my orig post the var name that has the .

Adding n to this variable still produces an error saying invalid column name.  Any ideas on how to resolve this?

 

I currently have

"AB. ADD"n='char(13)'

Occasional Contributor
Posts: 12

Re: how to write DBSASTYPE code for excel column names with symbols

Using a # helped with the . in the name
"AB# ADD"n='char(13)'
Super User
Posts: 5,853

Re: how to write DBSASTYPE code for excel column names with symbols

At this point it seem easier to rename the columns in Excel instead.
That said, check that you have
OPTIONS VALIDVARNAME=ANY;
Data never sleeps
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 120 views
  • 0 likes
  • 3 in conversation