topic Re: Escape character in data step in SAS Programming

Escape character in data step

helannivas88 — Tue, 28 Jul 2020 15:54:12 GMT

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.

Re: Escape character in data step

PaigeMiller — Tue, 28 Jul 2020 15:59:43 GMT

Please show us what you are seeing

Re: Escape character in data step

helannivas88 — Tue, 28 Jul 2020 16:18:35 GMT

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.

Re: Escape character in data step

PaigeMiller — Tue, 28 Jul 2020 16:21:51 GMT

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

Re: Escape character in data step

Tom — Tue, 28 Jul 2020 16:26:03 GMT

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-file-variations/idi-p/435977

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.

Re: Escape character in data step

Tom — Tue, 28 Jul 2020 16:32:14 GMT

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?

Re: Escape character in data step

helannivas88 — Tue, 28 Jul 2020 16:37:54 GMT

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

Re: Escape character in data step

Tom — Tue, 28 Jul 2020 17:12:30 GMT

@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;

Re: Escape character in data step

ballardw — Tue, 28 Jul 2020 17:43:54 GMT

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.