SAS Export dataset as csv or excel preserving line break

priv — Wed, 29 Aug 2018 16:22:01 GMT

I am trying to export my dataset from SAS to excel either is csv or xls format however, when I do this the columns with line breaks messes up my excel. Is there a way to export SAS dataset to excel preserving line breaks? I also need to display labels instead of column names and the dataset is fairly large approx. 150,000 rows.

Here is what I did,

proc export data=Final_w_label
outfile='work/ExtractExcel.csv'
dbms=csv label replace;
run; quit;

Thank you in advance.

Re: SAS Export dataset as csv or excel preserving line break

ChrisNZ — Thu, 30 Aug 2018 23:51:28 GMT

If you quote the variables with a break, Excel will recognise them:

data _null_;
  file "\\&path\test.csv";
  put 'a,b';
  put 'aa,bbbb';
  put 'aa,"bb' '0a'x 'bb"';
run;

Re: SAS Export dataset as csv or excel preserving line break

Tom — Fri, 31 Aug 2018 01:35:05 GMT

Excel can recognize embedded line breaks as long as the value is quoted.

Write the CSV file yourself instead of depending on a proc. It will be faster anyway. Then if you know which columns MIGHT include the line break you can force SAS to write the value as quoted by using the ~ modifier on the PUT statement.

Let's do a little test using SASHELP.CARS as our example.

data test;
 set sashelp.cars (obs=5 keep=
    make                             /* $13.   */
    model                            /* $40.   */
    weight                           /* best12. Weight (LBS) */
    wheelbase                        /* best12. Wheelbase (IN) */
    length                           /* best12. Length (IN) */
);
run;

So assuming that MODEL is the variable that might contain line breaks our output program might look like this.

filename csv temp;
proc export data=test(obs=0) outfile=csv dbms=csv label ; run;

data _null_;
  set test;
  file csv dsd mod ;
  put make model ~ weight wheelbase length ;
run;

And the result:

482  data _null_;
483   infile csv ;
484   input;
485   list;
486  run;

NOTE: The infile CSV is:
      Filename=...

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9
1         "Make","Model","Weight (LBS)","Wheelbase (IN)","Length (IN)" 60
2         Acura,"MDX",4451,106,189 24
3         Acura,"RSX Type S 2dr",2778,101,172 35
4         Acura,"TSX 4dr",3230,105,183 28
5         Acura,"TL 4dr",3575,108,186 27
6         Acura,"3.5 RL 4dr",3880,115,197 31
NOTE: 6 records were read from the infile CSV.
      The minimum record length was 24.
      The maximum record length was 60.

If you don't mind generating a lot of extra quotes you could just add the ~ modifier to every variable. Then you don't need to know the variable is the last step.

data _null_;
  set test;
  file csv dsd mod ;
  put (_all_) (~);
run;

topic SAS Export dataset as csv or excel preserving line break in SAS Programming

SAS Export dataset as csv or excel preserving line break

Re: SAS Export dataset as csv or excel preserving line break

Re: SAS Export dataset as csv or excel preserving line break