topic How to add a row at the top of the excel output file in SAS Programming

How to add a row at the top of the excel output file

bibbnd — Wed, 03 May 2023 01:12:36 GMT

I need to add 1 line of text and a space row at the top of the excel output file.

so row 1 should be ' Email ' and row 2 should be blank . than i want the data to start.

tried the following

ODS EXCEL file="sasdata/customer/bibbn/output/IQPMBLStuck_05022023.xlsx" ;
title1 'Email Address (Required) email@gmail.com'.
TITLE2 ' ';

proc print data=TENANT3 noobs;
run;

ODS EXCEL CLOSE;

it just says the file doesn't exist

so the file would open and look like :

Email require: email@gmail.com

blank row

id number address city state ca zipcode

Re: How to add a row at the top of the excel output file

ballardw — Wed, 03 May 2023 06:30:40 GMT

@bibbnd wrote:

I need to add 1 line of text and a space row at the top of the excel output file.

so row 1 should be ' Email ' and row 2 should be blank . than i want the data to start.

tried the following

ODS EXCEL file="sasdata/customer/bibbn/output/IQPMBLStuck_05022023.xlsx" ;
title1 'Email Address (Required) email@gmail.com'.
TITLE2 ' ';

proc print data=TENANT3 noobs;
run;

ODS EXCEL CLOSE;

it just says the file doesn't exist

so the file would open and look like :

Email require: email@gmail.com

blank row

id number address city state ca zipcode

So your problem is the PATH to the file likely doesn't exist. Best practice with SAS and files to always start with a Drive (in Windows) or disc mount point. Otherwise the path is relative to where SAS is executing which typically means looking for folders in the file system where the program is installed. Those subfolders typically won't exist and/or your IT department may have the program folders locked down so that you cannot write into them.

Hint: when you get errors you should include the log with the information. That means copy the CODE and all the messages from the log, on the forum open a text box using the </> icon above the message window and paste the text there. The text box is important because it preserves formatting of the diagnostic characters that SAS often provides.

I suspect that your "file not found" includes some additional path information that precedes "sasdata/customer/bibbn/output/IQPMBLStuck_05022023.xlsx" .

Re: How to add a row at the top of the excel output file

yabwon — Wed, 03 May 2023 07:27:29 GMT

Read the documentation for ODS EXCEL. Especially Example 1.

Code:

ODS EXCEL file="R:/IQPMBLStuck_05022023.xlsx" 
options(embedded_titles="yes");
;
title1 'Email Address (Required) email@gmail.com';

proc print data=sashelp.class noobs;
run;

ODS EXCEL CLOSE;

Bart