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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.