BookmarkSubscribeRSS Feed
Filipvdr
Pyrite | Level 9

hello,

 

I want to write a dataset to a SQL server table, though I have several columns with special characters (because of a transpose). Is it possible?

 

My libname statement

libname NB3DS_01 SQLSVR  READBUFF=32000  INSERTBUFF=32000  DELETE_MULT_ROWS=YES  UPDATE_MULT_ROWS=YES  Datasrc=BISQL1  SCHEMA=NB3DS_01  AUTHDOMAIN="Auth_BISQL1" ;

Knipsel.PNG

proc append base=NB3DS_01.MDM_PQ_&file_type._&current_month data=temp_transp_&i. force;
run;

 

The error:

ERROR: During insert: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name
'NB3DS_01.MDM_PQ_reg_202107'. Error in parameter 1.

 

When I would create the first variable in SQL server management studio, he put's brackets around the column name. How can I force this with SAS?

 

Knipsel2.PNG

2 REPLIES 2
Ksharp
Super User
Check
PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= LIBNAME options:
Oligolas
Barite | Level 11

Hi,

in SAS you have to adhere to the Rules for Most SAS Names when creating a dataset

Special characters, except for the underscore, are not allowed.

 

After a proc import for example from an excel file by using options validvarname=any; you could have datasets not respecting these rules.

To further proceed, the solution is to rename the variables like this:

PROC IMPORT out=test1 <..>RUN;
data test2;
 set test1;
rename   'Some strange variable name'n=var1;
run;

It's not clear to me if you can write out these special characters to a SQL server table, but it might be worth to try 'varname'n

 

________________________

- Cheers -

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 2 replies
  • 588 views
  • 0 likes
  • 3 in conversation