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

Holds a string with a maximum length of 65,535 bytes ... View more

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       ... View more

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)  ... View more

Hi @Patrick ,   Thanks a lot for this! ... View more

Hi @Ksharp  Your Solution also works perfectly.  Awesome! Thank you ... View more

Expected Output is :   Order,Document,First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS O-AMS123,D-AMS342,Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1 O-AMS797,D-AMS562,James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2 O-AMS797,D-AMS562,Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3 ... View more

data is as follows in CSV First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS Order #: O-AMS123,,,,,,,,,, Document #: D-AMS342,,,,,,,,,, Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1 Order #: O-AMS797,,,,,,,,,, Document #: D-AMS562,,,,,,,,,, James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2 Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3   ... View more