topic Re: How to load a varchar data from sasdataset to a text datatype column in sql table? in SAS Programming

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

sumi_saslearner — Mon, 08 Jul 2024 14:23:48 GMT

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?

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

yabwon — Mon, 08 Jul 2024 14:57:22 GMT

1) what code do you use?

2) what errors/log messages do you get?

3) what database?

Bart

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

sumi_saslearner — Tue, 09 Jul 2024 09:32:30 GMT

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)

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

yabwon — Tue, 09 Jul 2024 10:24:46 GMT

what is the result of:

proc contents data=SAMPLE_DATA;
run;

(variables attributes)?

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

sumi_saslearner — Tue, 09 Jul 2024 13:59:44 GMT

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

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

yabwon — Tue, 09 Jul 2024 14:39:59 GMT

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

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

Sajid01 — Tue, 09 Jul 2024 17:21:22 GMT

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-server/

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

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

Tom — Tue, 09 Jul 2024 17:32:41 GMT

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.

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

sumi_saslearner — Wed, 10 Jul 2024 06:17:47 GMT

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

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

sumi_saslearner — Wed, 10 Jul 2024 06:19:12 GMT

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