How to load a varchar data from sasdataset to a text datatype column in sql table?

[sumi_saslearner]

Hi,

I am loading a table in sql from a sasdataset. There are a few columns in the sql table with the datatype "text". 

Those which are in sasdataset are "varchar" type. So when i try to load I am getting datatype mismatch. How to match the datatype of "varchar" in sas dataset with "text" column in sql table?

[yabwon]

1) what code do you use?

2) what errors/log messages do you get?

3) what database?

Bart

[sumi_saslearner]

using sql server

sas code:

DATA SAMPLE_DATA;

SET SAMPLE_DATA1;

FORMAT COL_VALUE_20 $10000.

COL_VALUE_21 $10000.

COL_VALUE_22 $10000.

COL_VALUE_23 $10000.;

COL_VALUE_50 = ENTITY_KEY;

COL_VALUE_49 = NPN;

COL_VALUE_48 = TIN;

COL_VALUE_20 = tranwrd(OWNER,'0D'x,'$|**|$');

COL_VALUE_21 = tranwrd(IMPACTFUL_AGENT,'0D'x,'$|**|$');

COL_VALUE_22 = tranwrd(BOB_BY_ST,'0D'x,'$|**|$');

COL_VALUE_23 = tranwrd(TOP_DOWNLINE,'0D'x,'$|**|$') ;

RUN;

PROC SQL;

INSERT INTO D.SAMPLE

(COL_VALUE_50,COL_VALUE_49,COL_VALUE_48,COL_VALUE_20, COL_VALUE_21,COL_VALUE_22,COL_VALUE_23)

Select COL_VALUE_50,COL_VALUE_49,COL_VALUE_48,COL_VALUE_20, COL_VALUE_21,COL_VALUE_22,COL_VALUE_23 

From SAMPLE_DATA;

QUIT;

ERROR LOG: 

WARNING: Character expression will be truncated when assigned to character 

         column COL_VALUE_20.

WARNING: Character expression will be truncated when assigned to character 

         column COL_VALUE_21.

ERROR: Value 20 on the SELECT clause does not match the data type of the 

       corresponding column listed after the INSERT table name.

ERROR: Value 21 on the SELECT clause does not match the data type of the 

       corresponding column listed after the INSERT table name.

WARNING: Character expression will be truncated when assigned to character 

         column COL_VALUE_22.

WARNING: Character expression will be truncated when assigned to character 

         column COL_VALUE_23.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of 

      statements.

37         QUIT;

SQL TABLE STRUCTURE:

CREATE TABLE [dbo].SAMPLE(

[STATUS_IND] [smallint] NULL,

[COL_VALUE_20] [text] NULL,

[COL_VALUE_21] [text] NULL,

[COL_VALUE_22] [text] NULL,

[COL_VALUE_23] [text] NULL,

[COL_VALUE_24] [text] NULL,

[COL_VALUE_25] [text] NULL,

[COL_VALUE_26] [text] NULL,

[COL_VALUE_27] [text] NULL,

[COL_VALUE_28] [text] NULL,

[COL_VALUE_29] [text] NULL,

[COL_VALUE_30] [text] NULL,

[COL_VALUE_46] [varchar](255) NULL,

[COL_VALUE_47] [varchar](255) NULL,

[COL_VALUE_48] [varchar](255) NULL,

[COL_VALUE_49] [varchar](255) NULL,

[COL_VALUE_50] [varchar](255) NULL) 

[yabwon]

what is the result of:

proc contents data=SAMPLE_DATA;
run;

(variables attributes)?

[sumi_saslearner]

This is the output of proc contents

SAS Output
Alphabetic List of Variables and Attributes
Variable	Type	Len	Format	Informat	Label
COL_VALUE_20	Char	10000	$10,000.00
COL_VALUE_21	Char	10000	$10,000.00
COL_VALUE_22	Char	10000	$10,000.00
COL_VALUE_23	Char	10000	$10,000.00
COL_VALUE_24	Char	200
COL_VALUE_25	Char	200
COL_VALUE_48	Char	10
COL_VALUE_49	Char	10
COL_VALUE_50	Char	10

[yabwon]

ok, and what is the default length for COL_VALUE_20 variable in the SQL Server (the TEXT type variable in the system setup)?

[Sajid01]

Hello @sumi_saslearner 
Have a look at the following reference
https://www.mssqltips.com/sqlservertip/2771/using-sas-access-and-proc-sql-to-save-sas-data-in-sql-se...

Please note SQL server does support varchar formats whereas in SAS, CAS supports varchar but not SAS 9.4 .

[sumi_saslearner]

Holds a string with a maximum length of 65,535 bytes

[Tom]

I would first try using the DBTYPE= dataset option to tell SAS what database type should be used for those variables.

If that does not work then you might have to upload to an another table that has normal variables and then use pass thru SQL to copy into the target table with the TEXT variables.

How much data are you moving?  if only a little the perhaps you can just generate the MS/SQL/SERVER code to insert the data and run that via SQL Pass thru.

[sumi_saslearner]

Thanks will try. For now, only small number of rows but it might increase as we go..