BookmarkSubscribeRSS Feed
NN
Quartz | Level 8 NN
Quartz | Level 8
Hi everyone,
I have a process where we use proc import (dbms=xlsx) to import an excel file.
We then use libanme ODBC to connect to an SQL server and create the imported table in SQLserver.

Now one of the columns in the excel has values with special characters like the circle bullet symbol.

When we import the excel into sas we can see the bullet in the sas dataset.
But when we transfer the data to the SQL server the bullet changes to a sort of right arrow -> .

We are on sas Unix environment with Latin1 encoding. I don't have the option of starting a session with UTF8 encoding.
Is there any other solution that I can try to fix this issue?
9 REPLIES 9
Reeza
Super User

@NN wrote:
Hi everyone,
I have a process where we use proc import (dbms=xlsx) to import an excel file.
We then use libanme ODBC to connect to an SQL server and create the imported table in SQLserver.

Now one of the columns in the excel has values with special characters like the circle bullet symbol.

When we import the excel into sas we can see the bullet in the sas dataset.
But when we transfer the data to the SQL server the bullet changes to a sort of right arrow -> .

We are on sas Unix environment with Latin1 encoding. I don't have the option of starting a session with UTF8 encoding.
Is there any other solution that I can try to fix this issue?

What does that represent in the Excel spreadsheet, what should it be?

NN
Quartz | Level 8 NN
Quartz | Level 8
So on the excel I have a column B
And in one of the cells let's say {B2}, I have values like
• point1
• point2
• point3
The round bullet before the word point appears correct when imported into sas dataset .
But when the Sas dataset is used to create the data on SQL server the bullet appears as -> instead of •
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

are you sure that it is SAS and not SQL that is changing your char(7) or char(149) into this ->

NN
Quartz | Level 8 NN
Quartz | Level 8
I am honestly not sure if it's the SQL which is changing it.
I have used the condition Dbtype=nvarchar for the variable while setting the sas dataset to sql.

Also if I was to use a session with UTF8 encoding then these characters get transferred correctly.
But I am trying to see if there is any solution available while using the Latin1 session itself.
ChrisNZ
Tourmaline | Level 20

Just an idea. Have you tried using the INENCODING= and OUTENCODING= options?

 

SuryaKiran
Meteorite | Level 14

I guess your SAS and SQL Server needs to have same encoding.

Thanks,
Suryakiran
NN
Quartz | Level 8 NN
Quartz | Level 8
I am using libname odbc , hence the inecoding and outencoding may not work
NN
Quartz | Level 8 NN
Quartz | Level 8
Hi Surya,
Can you guide , how can I check the SQLserver encoding.?
SuryaKiran
Meteorite | Level 14

Try this: SELECT SERVERPROPERTY('Collation')

Thanks,
Suryakiran

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 3400 views
  • 0 likes
  • 5 in conversation