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._¤t_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?
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 -
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.