EXCEL.Insert text with new line char into one cell.

[Yura2301]

Hi,

Would like to insert content of small SAS table with one column into one excel cell, each of SAS tables row should be pasted directly after previous into one cell,manually it achives by ALT+ENTER (new line in excel, or char(10) in vba).

But I would like not to use additional vba scripts for formatting inserted text, so is there some work around?

I tried to concat all rows data into one column(catt(...)) with all possible delimiters('0D'x or '\n' etc.) but anyway data in excel stores in one row, no automatically move to row down, what I would like to achive...

For example:

data test;

      length a $ 32767;

      a=catt("test      ",'0D'x,"   test1");

run;

proc export

  data=test

  dbms=xls

  outfile="…\test.xls"

  replace;

run;

Will b e appreciate for any help.

[RW9]

That's an Excel specific item, I am not sure how you would generate that from SAS code.  Tagset export will strip non-valids, although you could write it yourself.

Alternatively write the xlsx itself.

One other option is to export data as CSV, then have a template program which loads the CSV into Excel and formats it as you want.

I would question why you want tabular output - which is what SAS produces and uses - in a non-tabular format however?  For instance you can do this in RTF/PDF, but going from tabular to tabular seems a bit odd to try to fit non-tabular things in.  As always Excel is not for <insert what you are trying to do> hence it will not be straight forward.

[ballardw]

The CATT function may well be treating your newline as a whitespace character and removing it.

I would try

'0D'x,"test      ","   test1");

but I can't since I don't have the modules to do dbms=xls.

[Peter_C]

PROC SQL noprint ;

select var into :macvar separated by '0A'x

from that small.table

is how I would collect the values

Use it where-ever like

   "&macvar"

Perhaps as the label for the column