topic Re: EXCEL.Insert text with new line char into one cell. in ODS and Base Reporting

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

Yura2301 — Mon, 01 Dec 2014 13:02:31 GMT

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.

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

RW9 — Mon, 01 Dec 2014 15:06:26 GMT

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.

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

ballardw — Mon, 01 Dec 2014 16:44:55 GMT

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.

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

Peter_C — Mon, 01 Dec 2014 17:34:16 GMT

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