writing dataset with several special column names to SQL server

[Filipvdr]

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" ;

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?

[Ksharp]

Check
PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= LIBNAME options:

[Oligolas]

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