Escape character in data step

[helannivas88]

Hi,

From the source extraction ,we are getting character like pipe & escape character and when we are loading into the destination db ,its causing an issue. We are extracting the data in a work dataset and then exporting into the .txt file and then loading into db separately  via external table load in db2.

We have thought to enclose double quotes or introduce a  escape char , if we face any such situation.

Whenever we have pipe value and did proc export, then SAS automatically enclose the column value with double quotes, which is good.

But if we have escape character, then how to introduce double quotes or another escape character to that column please.

Please let us know. Thanks in advance.

[PaigeMiller]

Please show us what you are seeing

[helannivas88]

While exporting, we are using pipe delimiter, so in the .txt file we have got below like this

246503|Y|"51 |THE FARMER"|USA.

51 |THE FARMER is a single value and that has been enclosed with double quoted.

But for the escape characters, we have value like this.

34604136|QUTE|WRB|ABB PARTNERSHIP\REG SRD\AB

We need ABB PARTNERSHIP\REG SRD\AB to be enclosed with double quotes.

[PaigeMiller]

How are you exporting? Can you show us the code?

[helannivas88]

proc export data=work.TEMP_TEST
outfile=/shared/data/Test/ABC.txt
dbms=dlm replace;
PUTNAMES=NO;
delimiter='|';
run;

[Tom · Accepted solution]

---

@helannivas88 wrote:

proc export data=work.TEMP_TEST
outfile=/shared/data/Test/ABC.txt
dbms=dlm replace;
PUTNAMES=NO;
delimiter='|';
run;

---

Note that a faster way to do that is to just use a data step.

data _null_;
   set work.TEMP_TEST;
   file "/shared/data/Test/ABC.txt" dsd dlm='|' ;
   put (_all_) (+0);
run;

But that wouldn't actually change the values written.  But as long as your character values are long enough to hold the extra "escape" characters you could pre-process the data to add them.  For example to convert all of the backslashes to double backslashes you could use TRANWRD() function call.

data _null_;
   set work.TEMP_TEST;
   file "/shared/data/Test/ABC.txt" dsd dlm='|' ;
   array _c _character_;
   do over _c;
     _c=tranwrd(_c,'\','\\');
   end;
   put (_all_) (+0);
run;

[Tom]

So you need to force quoting of values that contain a backslash?

Why?

Can't the system that is reading the file disable treating \ as a special character?

If not could you instead convert the \ to \\ to "escape" it?

[Tom]

Reading between the lines are your post it sounds like you are extracting data from some source (external database perhaps?) and want to process it in SAS and then create a new text file you can load into some target (the same or another external database).

Many databases use Unix style "escape" characters instead of quoting to protect delimiters in delimited data files.  SAS does not directly support that style.  https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhancements-to-INFILE-FILE-to-handle-delimited-...

You should first check if your source and target system can work with NORMAL delimited files that follow basic RFC-4180 standards.  Using that will not only make it easier to use the text files with SAS it will also make them compatible with other software.

[ballardw]

Please show the code you are using to "export" into a txt file.

Then show a few lines of that exported text file. Copy from the text file using a plain text viewer or editor, NOT a word processing program, database, html or other viewer. Paste into a code box opened on the forum with the </> icon. The message windows here will reformat pasted text, introducing html characters, change blank space and otherwise rending the text different than is actually in the file.

It is also best to post the code from the log that generates the output the same way.

It may also be a good idea to show how you bring the data into SAS.