enclose with double quotation mark

[Tom]

Please be more systematic is what you are testing and how you reporting your results here.

What did you do to create the SAS dataset?  Did you read in a text file, like you implied with the snippet of an INPUT statement?  Did you run that code on an IBM mainframe or on an ASCII based machine?

What does the SAS dataset you now have look like?  How many variables are their? How many observations?  Can you just print the data?  Does it look right?  If not then perhaps there is something wrong with the step that created the dataset from the text file.

How did you try to create the CSV file?  Did you write your own data step?  Did you let PROC EXPORT write the data step for you?

If you are confused about what the text files look like then take advantage of the LIST statement in a data step to display what is in the text file.   So run something like this on both the orignal text file and on the CSV File you created.

data _null_;
   infile textfile obs=5;
   input;
   list;
run;

How are you trying to load the CSV into DB2?  Why didn't you just tell DB2 to read the original text file? Can DB2 not do that?

[HeatherNewton]

so this is the sequence
read from text file

data SDATA2.HKPBIM_STATIC;
infile "&G-IRF_RAW_PENDING_PATH./hkpb.static.txt" RECFM=N'
readdata:
input hkpbim_int_rate $EBCDIC4.0
.
.
.
FORMAT hkpbim_int_rate $4.0

THEN SDATA2.HKPBIM_STATIC is used to create wdata.installment using data step and nothing is done to the variable  hkpbim_int_rate  and wdata.installment is the file I want to convert to csv using the following code:
filename exprt "/toutput/path/installment.csv" encoding="utf-8";
proc export data=output.installment outfile=exprt dbms=csv replace; run;

now in the output most lines can be loaded into db2 using load insert command but as shown in picture attached where red line added, this row suddenly cannot be loaded because the input splitted into two lines unlike lines before and after it. I was told it was seperated by a 'cache return', not too sure but it sound likes it from a senior..

and senior asked me to put "" in the field to resolve. I tried but I end up getting """"""""""""" as the input instead very weird. so my question is how to have this column load into db2 properly, right now it gets rejected? I will show you how sas display these in a bit.... I must convert into csv first..

[HeatherNewton]

oh in sas it is actually blank

see attached next to 1077.27 (row 9243, col hkpbim_int_rate)

[Kurt_Bremser]

"Blank" in the SAS viewtable can easily mean "filled with non-displayable" characters.

Look at the real contents of the variable by displaying in with a $HEX format.

In your screenshot, I see a lot of "funny" characters in the csv file. How did they come about? Is this some kind of binary string in the original mainframe source, or was something "funny" done to it (like your mostly useless "shift by 55" masking talked about in your other thread)?

[HeatherNewton]

haha not the 55 things,

it just came in as a txt file

then we use sas to read from text file and output as dataset , that's it

I am trying to find that txt file and will show you if I can have access

[HeatherNewton]

in notepad it looks gibberish too

in ultraedit, using hex

it shows better....

pls see both pic

[Kurt_Bremser]

No external editor. Look at the variable in the SAS dataset with a $HEX format.

What does the description of the file say about the column? 4 bytes coming from a mainframe can easily contain a binary number.

[Tom]

Photographs are not the best way to share data.  looks like it read the values fine.

Note that spaces in ASCII are 20 in hexadecimal.  But they are 40 in EBCIDIC, which is an @ in ASCII.

How did you move the file to the machine that is loading the data? Perhaps it tried to convert ASCII back into EBCDIC?

If the issue is that one or more of the character values has has an carriage return and or line feed character in them then you should replace those characters with something else.  You can use the TRANSLATE() function to do that.  For example to translate all of the CR and LF characters in the a dataset to spaces you could use a data step like this:

data fixed;
  set have;
  array _c _character_;
  do over _c;
     _c = translate(_c,'  ','0D0A'x);
  end;
run;

[HeatherNewton]

We convert sasdataset into csv using encoding utf8 as explained above
Then csv is transferred to linux and load into db2

[HeatherNewton]

I convert sas dataset with those symbols into csv using utf8

then sftp to another server which load insert into db2

[Kurt_Bremser]

What we see here are the informats used to read the data from an external file. How the data is displayed is controlled by the formats, so we need to see those.

[ballardw]

---

@HeatherNewton wrote:

if I want to enclose content of a field with double quotation mark, how to do

e.g. hello becomes "hello"

also if I want add .000000 to content of a field, how do I do it?

---

Warning: Any insertion of characters to a variable may fail because of length issues. If your variable is defined as 8 characters and has a current value like ABCDEFG (7 characters total) then adding quotes would attempt to be "ABCDEFG" for a total of 9 characters. Since the variable in the example is defined as 8 then anything over 8 would be discarded.

Is that .000000 to be added to a character or a numeric variable? If character, see the above note. Adding 7 characters is more likely to cause truncation issues than adding 2. If numeric change the format to one that forces enough decimal positions, such as 16.7 . But without knowing the range of values can't tell you the base format to use. Be aware that if you export such an read it with any spreadsheet software those trailing 0 are very likely to disappear for a column treated as numeric.