topic writing dataset with several special column names to SQL server in SAS Programming

writing dataset with several special column names to SQL server

Filipvdr — Fri, 09 Jul 2021 07:52:14 GMT

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?

Re: writing dataset with several special column names to SQL server

Ksharp — Fri, 09 Jul 2021 12:19:39 GMT

Check
PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= LIBNAME options:

Re: writing dataset with several special column names to SQL server

Oligolas — Fri, 09 Jul 2021 12:35:46 GMT

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