0

I am attempting to insert a SAS dataset into an existing table on a SQL Server. This is via a simple proc sql statement.

proc sql;
    insert into Repo.Test_Table
    select * from Work.MetaTable;
quit;

One of the fields, [Method], is not inserting into the SQL table as expected. The [Method] field in the SAS table contains several brackets and other punctuation so I think this is causing a problem. For example, the Work.MetaTable looks like this:

Field_ID Method
1 ([Field_1]<=[Field_8])
2 ([Field_4]=[Field_5])

When I run the proc sql to insert this into SQL, it only inserts the first open bracket "(" and this is the case for every row. For example, those two rows look like this in the SQL table:

Field_ID Method
1 (
2 (

The [Method] field in SQL is nvarchar(max).

Does anyone know what might be causing the issue here and how I can get around it?

Alex
  • 3
  • 2
  • How is the target variable defined? What is the maximum length that it can hold? Looks like it is currently defined to only hold 1 byte. – Tom Aug 02 '21 at 12:51
  • @Tom Target variable is a nvarchar(max) in SQL. It already contains correct data that was uploaded via other methods. Just seems to be an issue when coming from SAS. – Alex Aug 02 '21 at 12:58
  • @Tom I found the issue. SAS for some reason sees a nvarchar(max) field as nvarchar(1), so as you said it was truncating the data. I've tried uploading to a temporary table where [Method] is defined as nvarchar(500), and this works fine, but then again insert into the nvarchar(max) field from the temporary table still truncates the data! – Alex Aug 02 '21 at 13:28
  • That seems correct, from my past experience with varchar(max) or nvarchar(max). (Also, thanks for having this problem - made me realize I need to rewrite my DDL for some tables I'm currently working with!!) – Joe Aug 02 '21 at 15:14

0 Answers0