BookmarkSubscribeRSS Feed
priv
Calcite | Level 5

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.

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

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;

 

Tom
Super User Tom
Super User

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 4103 views
  • 2 likes
  • 3 in conversation