topic Re: Export a SAS dataset to csv with termstr option in SAS Programming

Export a SAS dataset to csv with termstr option

Chrisas — Mon, 11 Apr 2022 16:29:59 GMT

Hi everyone,

I am trying to export my SAS dataset to csv. However, due to line breaks existing in some values of the SAS Dataset, my csv is messed up.

I using the option termstr=CRLF in the proc export command, as it is used in the proc import one. When I import a csv to a SAS dataset, the prementioned option works.

However, when I export the SAS dataset to csv, this option does not work. Specifically, the below code is what I am trying to run:

filename out "/path/test.csv" termstr=crlf;

proc export data=work.check
outfile=out dbms=csv replace;
delimiter="|";
run;

Do you know why the above code does not fix the line breaks and does not specialize the end-of-line?

Thank you in advance!

Re: Export a SAS dataset to csv with termstr option

Tom — Mon, 11 Apr 2022 17:34:16 GMT

How is it not working? What do you mean by "fixed the line breaks"?

It works fine for me. Here is code to test it.

filename csv temp termstr=crlf;

proc export data=sashelp.class(obs=1) file=csv dbms=csv replace;
  delimiter='|';
run;

data _null_;
  infile csv recfm=f lrecl=100;
  input;
  list;
run;

As you can see from the results the values are separated by | and the lines end with CRLF characters.

RULE:     ----+----1----+----2----+----3----+----4----+----5----+

1   CHAR  Name|Sex|Age|Height|Weight..Alfred|M|14|69|112.5.. 50
    ZONE  46667567746674666677566667004667667473373373332300
    NUMR  E1D5C358C175C859784C759784DA1C6254CDC14C69C112E5DA

Re: Export a SAS dataset to csv with termstr option

AMSAS — Mon, 11 Apr 2022 17:35:11 GMT

TERMSTR= documentation

Can you provide a sample of the dataset you are exporting?

Data2DataStep Maco instructions will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Re: Export a SAS dataset to csv with termstr option

Sajid01 — Mon, 11 Apr 2022 19:00:06 GMT

Hello @Chrisas
Is your SAS running on Linux/Unix and are you using the csv created on Windows?

By default on a typical Linux/Unix system LF (line feed) automatically introduces a carriage return (CR), whereas on Windows OS both CR and LF needs to be given.

Do let us know your OS and the log.

Re: Export a SAS dataset to csv with termstr option

Tom — Mon, 11 Apr 2022 19:42:18 GMT

Do you mean the text in the file contains end of line characters so that the resulting text file cannot be read by SAS?

Try converting any CRLF characters in the data into just CR characters before writing the file.

data for_export / view=for_export ;
  set check;
  array _character_ (_n_) _character_;
  do over _character_;
    _character_=tranwrd(_character_,'0D0A'x,'0D'x);
  end;
run;

filename out "/path/test.csv" termstr=crlf;
proc export data=for_export
  outfile=out dbms=csv replace
;
  delimiter="|";
run;

Re: Export a SAS dataset to csv with termstr option

Chrisas — Tue, 12 Apr 2022 06:59:37 GMT

Hi Tom. Thank you very much for your help!

Instead of "tranwrd", I used the "translate" command and it worked.

data for_export / view=for_export ; set check; array _character_ (_n_) _character_; do over _character_; _character_=translate(_character_,' ','0D0A'x); end; run; filename out "/path/test.csv" termstr=crlf; proc export data=for_export outfile=out dbms=csv replace ; delimiter="|"; run;