Line break in ods

[filippo_kow]

 Hi guys,

Before I ask the question let me paste the code:

%let string = "This is line 1 <line_break> This is line 2 <line_break> This is line 3";

data test;
	var=&string.;
run;

proc sql;
	select * from test;
quit;

I want to have a dataset with only one observation, but in the final ODS I want to show one line above another. I need this because later on I am using stored process to fill data in the excel file, so I want to fill the data in the following way:

Btw. I was also trying to prepare the formula for excel in SAS:

but for some reason excel doedn't want to parse it properly (I have "#name?" error after applying somethink like this: sheet_1.Cells(16, 5).Value = my_data.Cells(2, 30) (what's interesting it is working when I press F2 and Enter on the cell) ).

Thanks in advance for any help!

 Filip

[Cynthia_sas]

Hi:

  It's not clear to me what you're doing or what you want. You don't show any ODS statements. You say that you want to "fill data in" the Excel file but it's not clear to me whether the data is coming from SAS or not. I'm not sure why you need a formula. Also, <line break> is not any ODS method for inserting a line feed or line break. And, you have a DATA step that makes your variable VAR from &string. Why do you need the PROC SQL step?

Cynthia

[filippo_kow]

 Hi @Cynthia_sas ,

Thanks for your reply.

In general I need to fill excel file based on data from SAS. What I am doing is:

1. I have created a stored process that selects data from SAS. In that stored process I am gathering all the data and finally I am running the following proc sql:

proc sql;
 select * from my_data;
quit;

2. Then, in the excel file I am running the process from SAS add-in. In the 'results' pane of add-in I see data from my last query (select * from my_data).

3. I am inserting resulting data into new excel sheet

4. Based on those results I need to fill form that is in another sheet.

5. Here comes my question: sometimes it might happen that for one observation I can have multiple values (in one row), eg.:

6. In my final form I want to show those orders one above another:

7. Originally I thought I can create formula for excel in SAS ( ="Order_123"&char(10)&"Order_456"&char(10)&"Order_789") and when inserting it into excel it will be properly handled. Unfortunately, excel claims that there is an error in the formula. So what I am trying to do now is to add line breaks after each "order" so that in the final form all orders will be one above another (in the same cell).

I hope it is more clear now 🙂

 Thanks,

 Filip

[Tom · Accepted solution]

Use in-line style command {newline}.

Example:

ods escapechar='^';
data test;
  length string $200;
  string=catx('^{newline}','Order_123','Order_456','Order_789');
run;
ods excel file='c:\downloads\newline.xlsx';
proc print data=test;
run;
ods excel close;

Here is what the result looks like in Excel file:

And in the HTML output

Part of the confusion of your original question was your choice to show an SQL select statement instead one of the normal SAS procedures like PRINT, REPORT or TABULATE that is normally used to create a printed report of the data.

[Cynthia_sas]

@Tom: Thank you! That's exactly what I would have suggested (escapechar+newline). The other reason that the PROC SQL was confusing was that there were not any ODS Statements shown in the original code. So having the ODS EXCEL statements visible was/is essential.

Cynthia

[filippo_kow]

 Hi all,

Thanks a lot for your support! It works perfectly 🙂 

Just one remark, maybe it will be somehow useful for other people. When run the stored process from SAS Add-In (not using ods excel) we need to use proc report (instead from proc print).

 Thanks again!
 Filip