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;
... View more