topic Re: QUOTES ISSUES - while using proc export to text file in SAS Programming

QUOTES ISSUES - while using proc export to text file

shuchidxt_gmail_com — Wed, 13 Mar 2019 23:59:01 GMT

Hi,

I am trying to export a dataset into text file. when I am doing this...my text in the data look like as

No wire activity was identified during an extended search from July-September 2018. BAU All Trans report identified minimal activity; of note was a debit for $122K stating "OFAC/Funds moved to Omnibus". UAR# 4134354

but when it is writing to the text file it convert it to below adding quotes to the text as bold highligted

"No wire activity was identified during an extended search from July-September 2018. BAU All Trans report identified minimal activity; of note was a debit for $122K stating ""OFAC/Funds moved to Omnibus"". UAR# 4134354

Re: QUOTES ISSUES - while using proc export to text file

Tom — Thu, 14 Mar 2019 00:24:32 GMT

How did you "export" to the text file?

If tried to create a delimited file then SAS used the DSD option to allow for proper protection of delimiters. So if any data item contains the delimiter then the value is enclosed in quotes. But that means that it also need to protect values that contain the quote character itself to avoid confusion for values like:

"hello"

Re: QUOTES ISSUES - while using proc export to text file

shuchidxt_gmail_com — Thu, 14 Mar 2019 00:52:58 GMT

I use as below

proc export data=out.alerts_export outfile="&outbound_folder_path/%lowcase(&case_type_cd.)_alerts_export_&tbl..txt" replace dbms=dlm;
delimiter='|';
quit;

pipe delimited file

Re: QUOTES ISSUES - while using proc export to text file

ScottBass — Thu, 14 Mar 2019 01:03:55 GMT

See https://github.com/scottbass/SAS/blob/master/Macro/export.sas, perhaps it will help?

You'll also need to copy https://github.com/scottbass/SAS/blob/master/Macro/parmv.sas.

The concepts are copied from https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-use-labels-in-proc-export-or-create-tab-dlm-file-using/m-p/76789#M8698, so all the smarts of the macro are due to @data_null__.

I just put some macro wrapper code around his code.

Re: QUOTES ISSUES - while using proc export to text file

shuchidxt_gmail_com — Thu, 14 Mar 2019 02:14:22 GMT

So what is the solution?

Re: QUOTES ISSUES - while using proc export to text file

Tom — Thu, 14 Mar 2019 02:17:01 GMT

@shuchidxt_gmail_com wrote:
So what is the solution?

What is the problem? That is how delimited files are SUPPOSED to be made.

What are you reading it with that is not properly removing the added quoting?

Re: QUOTES ISSUES - while using proc export to text file

shuchidxt_gmail_com — Thu, 14 Mar 2019 12:02:45 GMT

Yes, we send this file to downstream application with 200 character long. Right now the length is 203 because of the extra quotes added.

their job will fail if we send them data more than 200 character long string.

Re: QUOTES ISSUES - while using proc export to text file

Tom — Thu, 14 Mar 2019 13:13:44 GMT

What application are you using that is misreading the file so that it thinks the value is 203 characters long?

For example if the downstream process is Amazon Redshift COPY command then make sure to include the CSV option so that it handles the quotes properly.

Re: QUOTES ISSUES - while using proc export to text file

Tom — Thu, 14 Mar 2019 13:54:54 GMT

If you MUST create a file that is using a non standard format then you will probably need to create the file using a data step.

But you will then have to make your own checks for how to handle values with embedded delimiters and how to handle missing values.

If you know that your data does not have either of these issues then your data step could be as simple as:

data _null_;
   set sashelp.class ;
   file log dlm='|';
   put (_all_) (:);
run;

Replace the LOG keyword with the quoted physical name of the file you want to create. Replace SASHELP.CLASS with the name of your file.

If you want the variable names added as a header row then you could just add a little more code.

filename csv temp;
proc transpose data=sashelp.class(obs=0) out=names; run;
data _null_;
  file csv dlm='|';
  set names end=eof;
  put _name_ @;
  if eof then put;
run;
data _null_;
  set sashelp.class ;
  file csv mod dlm='|';
  put (_all_) (:);
run;

Again replace the TEMP keyword with the quoted physical name of the file you want to create.