BookmarkSubscribeRSS Feed
Yura2301
Quartz | Level 8

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.

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3657 views
  • 0 likes
  • 4 in conversation