12-01-2014 08:02 AM
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...
length a $ 32767;
a=catt("test ",'0D'x," test1");
Will b e appreciate for any help.
12-01-2014 10:06 AM
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.
12-01-2014 11:44 AM
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.
12-01-2014 12:34 PM
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
Perhaps as the label for the column