topic Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG? in SAS Programming

How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Mdxolly — Thu, 23 Jan 2020 00:08:34 GMT

I have a SAS EG dataset that I've created through a series of SAS programs that I'm ready to export out to a text file (.txt). The problem is the requester is looking for a pipe delimited file with double quote (" ") text qualifiers around each value and each variable name. How do I do that?

I've tried going thru the export button on the final dataset I've created but SAS EG won't allow me to choose a pipe delimiter or even a text qualifier.

If there's no easy way to do this, how can I search my large dataset if there's a pipe symbol ( | ) in any of the values?

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

ballardw — Thu, 23 Jan 2020 00:40:11 GMT

You may need to clarify that each an every value, including numeric especially, needs to be quoted. Typically for most purposes the quotes are needed only for values that might contain the delimiter.

Since your described output is slightly nonstandard you have a couple of choices. One is to write a data _null_ step from scratch creating quoted values and then using PUT statements with the values to create the output.

Or possibly use Proc Export to create a delimited text file. The proc will write a data _null_ step to the log that you can modify.

A starting example:

Proc export data=sashelp.class
      outfile= "x:\data\filename.txt"
      dbms=dlm
     replace;
      delimiter="|" ;
run;

Will create a delimited file but the contents will look like:

Name|Sex|Age|Height|Weight
Alice|F|13|56.5|84
Barbara|F|13|65.3|98
Carol|F|14|62.8|102.5
Jane|F|12|59.8|84.5
Janet|F|15|62.5|112.5

The log will have code that looks like

5543      data _null_;
5544      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
5545      %let _EFIREC_ = 0;     /* clear export record count macro variable */
5546      file 'x:\data\filename.txt' delimiter='|' DSD DROPOVER lrecl=32767;
5547      if _n_ = 1 then        /* write column names or labels */
5548       do;
5549         put
5550            "Name"
5551         '|'
5552            "Sex"
5553         '|'
5554            "Age"
5555         '|'
5556            "Height"
5557         '|'
5558            "Weight"
5559         ;
5560       end;
5561     set  SASHELP.CLASS   end=EFIEOD;
5562         format Name $8. ;
5563         format Sex $1. ;
5564         format Age best12. ;
5565         format Height best12. ;
5566         format Weight best12. ;
5567       do;
5568         EFIOUT + 1;
5569         put Name $ @;
5570         put Sex $ @;
5571         put Age @;
5572         put Height @;
5573         put Weight ;
5574         ;
5575       end;
5576      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
5577      if EFIEOD then call symputx('_EFIREC_',EFIOUT);
5578      run;

You can copy that code into the editor as a code node and modify it to write the values as desired.

An incomplete example could be

data _null_;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
%let _EFIREC_ = 0;     /* clear export record count macro variable */
file 'x:\data\filename.txt' delimiter='|' 
      DROPOVER lrecl=32767;
if _n_ = 1 then        /* write column names or labels */
 do;
   put
      '"Name"'
   '|'
      '"Sex"'
   '|'
      '"Age"'
   '|'
      '"Height"'
   '|'
      '"Weight"'
   ;
 end;
set  SASHELP.CLASS   end=EFIEOD;
   format Name $8. ;
   format Sex $1. ;
   format Age f2. ;
   format Height best12. ;
   format Weight best12. ;
 do;
   EFIOUT + 1;
   nametext= quote(strip(name));
   sextext= quote(strip(sex));
   agetext= quote(put(age,f2.));
   Heighttext=   quote(strip(put(height,best6.)));
   put nametext  @;
   put Sextext  @;
   put Agetext @;
   put Heighttext @;
   put Weight ;
   ;
 end;
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

I'm sure there are lots of other ways as well but most will involve some combination of string creation and manipulation then writing to the file.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Mdxolly — Thu, 23 Jan 2020 01:01:07 GMT

Thank you for that. Is the difference between the log and the modified log that you included single quotes around the double quoted variables?

Also, where is the "editor as a code node"?

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Mdxolly — Thu, 23 Jan 2020 01:34:08 GMT

Also, a separate question but in a similar vein:

Is there a way to search my final sas dataset for a pipe delimiter? If I can incorporate this search, then I could forgo the text qualifier.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Reeza — Thu, 23 Jan 2020 04:04:25 GMT

data clean;
set sashelp.cars;

*convert all characters to quoted;
array myvars(*) _character_;
do i=1 to dim(myvars);
myvars(i) = quote(trim(myvars(i)));
end;

file '/folders/myfolders/demo.txt' dlm='|';
put (_all_) (:) ;
run;

This will work - but you'll need to ensure your variables have the appropriate length to handle two extra characters for the quotes. If not, you'll need a different approach.

If you want to search the file for the existence of any pipes, you could export it via CSV and search that file, via CTRL+F or if you want to use SAS to search the INDEX() function within a computed column in a query can do that for you.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Kurt_Bremser — Thu, 23 Jan 2020 07:12:23 GMT

See a simple example for exporting SASHELP.CLASS in this way:

data _null_;
set sashelp.class;
file '$HOME/sascommunity/class.txt';
if _n_ = 1 then put '"Name"|"Sex"|"Age"|"Height"|"Weight"';
line = catx('|',name,sex,age,height,weight);
line = cats('"',tranwrd(line,'|','"|"'),'"');
put line;
run;

For your dataset, it may be necessary to set a sufficient length for variable line and also a lrecl= in the file statement.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Tom — Thu, 23 Jan 2020 13:23:06 GMT

The first response would be back to the requester as to why they would want such a format. Perhaps they just think they need it because they misunderstood the reason that quotes are needed when a particular value includes the delimiter character.

Do you need quotes around ALL values? Or only around the character variable values? Around all values is easier.

Use the ~ modifier in the PUT statement to have SAS adds quotes, even when they are not required.

You can use a simple program like this to write the data for all variables with quotes.

data _null_;
  file myfile dsd dlm='|';
  set sashelp.class ;
  put (_all_) (~);
run;

If you need to add a header line then just write that first and then use the MOD option on the FILE statement on the step that writes the data lines.

%let dsn=sashelp.class ;
filename myfile 'physical file name.txt';

proc transpose data=&dsn(obs=0) out=names ;
  var _all_;
run;

data _null_;
  file myfile dsd dlm='|';
  set names end=eof;
  put _name_ ~ @;
  if eof then put;
run;

data _null_;
  set &dsn ;
  file myfile dsd dlm='|' mod;
  put (_all_) (~);
run;

You really shouldn't need to worry about whether any of the values in your data include actual pipe characters since you have already enclosed all of the values in quotes. But if whatever program that is reading the file is stupid enough to require quotes around all values then might also not understand how to handle values with embedded quote characters. If you have can actual double quote character in a value then SAS will double those characters so that the resulting string can be parsed. So

He said "hello" to me.

When quoted will become

"He said ""hello"" to me."

To scan all character variables in a dataset looking for particular character(s) you probably want to use an array.

data _null_;
  set &dsn ;
  array _c _character_;
  do index=1 to dim(_c);
    if indexc(_c[index],'"|') then put _n_= _c[index]= ;
  end;
run;

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Mdxolly — Thu, 23 Jan 2020 18:39:28 GMT

Hi Reeza,

this solution almost worked - it's missing the variable names (which I need in double quotes) and some of the missing values are a set of two double quotes while other missing values are represented by a single double quote which is shown below. Do you know why that is?

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Tom — Thu, 23 Jan 2020 19:07:01 GMT

You are getting single quote character because you cannot put three characters into a field that is only one character long. If you want to use that method of individually adding quotes to values then you need to make sure the variable has room for the quoted value. It would be better to loop over the variables and put the quoted value into a new variable. re-using it as you go across the row.

Also how does you system want missing values to be represented? Is it different for missing character values and missing numeric values?

Also please let us know what system you are going to load this text file into. If it is commercially available software perhaps it has settings to allow it to read a normal delimited file instead of one with so many unnecessary quote characters.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Reeza — Thu, 23 Jan 2020 19:58:50 GMT

On second thought all of this is overkill. If SAS find the delimiter in the field it will automatically quote that field for you.

This demonstrates the issue, so you can use the PROC EXPORT as I have it below and know that it will correctly export your data for a system. If your requirement is you must have quotes around every variable then use @Tom solution which is cleaner.

data demo;
set sashelp.class;
if name in ('Alfred', 'Jane') then  name = 'REP|Name';
run;

proc export data=demo file='/home/fkhurshed/Demo1/demo.txt' dbms=dlm replace;
delimiter='|';
run;

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Tom — Thu, 23 Jan 2020 20:15:55 GMT

If the requirement is just to quote the character values, and not the numeric values, then try ODS CSV. You can use teh DELIMITER option to ask it to use | instead comma.

ods csv file=csv options(delimiter='|');
options missing=' ';
proc print data=test; run;
ods csv close;
options missing='.';

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Adumb — Wed, 25 Aug 2021 18:56:47 GMT

I received the same kind of file request and this method works perfectly with one minor issue - null values appear in the output as a space (" ") instead of just two double quotes (""). The space is causing the upload to their system to fail.

Any idea how to remove these spaces?

Thank you in advance.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Reeza — Wed, 25 Aug 2021 19:21:43 GMT

ods csv file=csv options(delimiter='|');
options missing=' ';
proc print data=test; run;
ods csv close;
options missing='.';

Looking closely at the code, try changing the line in red and see if you can find a solution that works for you.

@Adumb wrote:

I received the same kind of file request and this method works perfectly with one minor issue - null values appear in the output as a space (" ") instead of just two double quotes (""). The space is causing the upload to their system to fail.

Any idea how to remove these spaces?

Thank you in advance.

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Tom — Wed, 25 Aug 2021 19:24:25 GMT

@Adumb wrote:

I received the same kind of file request and this method works perfectly with one minor issue - null values appear in the output as a space (" ") instead of just two double quotes (""). The space is causing the upload to their system to fail.

Any idea how to remove these spaces?

Thank you in advance.

First question is what system is imposing such restrictions on the CSV file format? Let's name and shame them. If no one complains they will never fix it.

So you require missing character variables to appear as

"abc"|""|"ghi"

Instead of either of these?

"abc"|" "|"ghi"
"abc"||"ghi"

What about missing numeric values?

Also does this mean you need to worry that the system might also be storing significant trailing spaces into character variables? That can cause a lot of confusion. For example would it consider these two rows as being different?

"abc"|"def"|"ghi"
"abc"|"def   "|"ghi"

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Adumb — Wed, 25 Aug 2021 19:46:47 GMT

I'm new to this group and I'm hoping this is a one-off request. I send the file off to a different team so I'm not sure what they're using. I will certainly be asking more questions.

Yes, this is the desired output:
"abc"|""|"ghi"

Currently I'm getting this:
"abc"|" "|"ghi"

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Tom — Wed, 25 Aug 2021 19:48:22 GMT

You could try making your own format that writes blank as bare quotes and then use that without the DSD option.

Example:

proc format;
value $equote (max=32767)
 ' '='""'
 other=[$quote.]
;
run;

data test;
  infile cards dsd truncover;
  input (c1-c3) (:$30.) n1-n3 ;
  file log dlm=',';
  format _character_ $equote.;
  put c1-c3 n1-n3 ;
cards;
1,2,3,4,5,6
a,b,,7,,9
;

Results:

"1","2","3",4,5,6
"a","b","",7,.,9

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

Tom — Wed, 25 Aug 2021 21:15:21 GMT

@Adumb wrote:
I'm new to this group and I'm hoping this is a one-off request. I send the file off to a different team so I'm not sure what they're using. I will certainly be asking more questions.

Yes, this is the desired output:
"abc"|""|"ghi"

Currently I'm getting this:
"abc"|" "|"ghi"

You can also try giving them a file that does not have any of the unnecessary quotes.

abc||ghi

There is no reason to have quotes unless the value contains the delimiter (or quotes).

Re: How do I export into a pipe delimited text file with text qualifiers in SAS EG?

ChrisNZ — Thu, 26 Aug 2021 08:01:13 GMT

An easier option:

data CLEAN;
  set SASHELP.CARS;
  format _character_ quote30.;
  file "&wdir/demo.txt" dlm='|';
  put (_all_) (:) ;
run;