CODEWITHSUNDEEP
sasdelimiter
JJFord3
JJFord3

Reputation: 1985

Exporting to a text file in SAS with a double delimitter

I'm trying to use a double pipe delimiter "||" when I export a file from SAS to txt. Unfortunately, it only seems to correctly delimit the header row and uses the single version for the data.

The code is:

proc export data=notes3 outfile='/file_location/notes3.txt'
dbms = dlm;
delimiter = '||';
run;

Which results in:

ID||VAR1||VAR2
1|0|STRING1
2|1|STRING2
3|1|STRING3

Upvotes: 0

Views: 6566

Answers (2)

user667489
user667489

Reputation: 9569

The reason proc export won't use a double pipe is because it generates a data step to do the export, which uses a file statement. This is a known limitation - quoting the help file:

Restriction: Even though a character string or character variable is accepted, only the first character of the string or variable is used as the output delimiter. This differs from INFILE DELIMITER= processing.

The header row || works because SAS constructs it as a string constant rather than using a file statement.

So I don't think you can fix the proc export code, but here's a quick and dirty data step that will transform the output into the desired format, provided that your dataset has no missing values and doesn't contain any pipe characters:

/*Export as before to temporary file, using non-printing TAB character as delimiter*/
proc export 
    data=sashelp.class 
    outfile="%sysfunc(pathname(work))\temp.txt"
    dbms = dlm;
    delimiter = '09'x;
run;

/*Replace TAB with double pipe for all rows beyond the 1st*/
data _null_;
 infile "%sysfunc(pathname(work))\temp.txt" lrecl = 32767;
 file "%sysfunc(pathname(work))\class.txt";
 input;
 length text $32767;
 text = _infile_;
 if _n_ > 1 then text = tranwrd(text,'09'x,'||');
 put text;
run;

/*View the resulting file in the log*/
data _null_;
    infile "%sysfunc(pathname(work))\class.txt";
  input;
    put _infile_;
run;

As Joe suggested, you could alternatively write your own delimiter logic in a dynamically generated data step, e.g.

/*More efficient option - write your own delimiter logic in a data step*/
proc sql noprint;
    select name into :VNAMES separated by ',' 
        from sashelp.vcolumn 
            where libname = "SASHELP" and memname = "CLASS";
quit;

data _null_;
    file "%sysfunc(pathname(work))\class.txt";
    set sashelp.class;
    length text $32767;
    text = catx('||',&VNAMES);
    put text;
run;

Upvotes: 1

Joe
Joe

Reputation: 63424

If you want to use a two character delimiter, you need to use dlmstr instead of dlm in the file statement in data step file creation. You can't use proc export, unfortunately, as that doesn't support dlmstr.

You can create your own proc export fairly easily, by using dictionary.columns or sashelp.vcolumn to construct the put statement. Feel free to ask more specific questions on that side if you need help with it, but search around for data driven output and you'll most likely find what you need.

Upvotes: 4

Related Questions