Saving data in multiple excel sheets

[UV_123]

Hi All,

I am running a macro for my program. I have multiple files as input to the macro and I want the output to be saved in only one Excel workbook but under different excel sheets. 

My macro function looks like this:

%macro i_data (string,file);

ods tagsets.ExcelXP file="designation/result.xls"

options (

sheet name = &string. /*string name used in macro function*/

sheet_interval = 'None'

);

proc print data = input.&file.;

run;

ods tagsets.ExcelXP close;

%mend ;

%i_data(ip1,file1);

%i_data(ip2,file2);

%i_data(ip3,file4);

I am getting an error in the above code. Please can someone help me with this?

[Reeza]

What's the error?

Your code as is, generates a new file with each call of the macro. You need to move the file creation outside of the macro if you want to run it for multiple strings.

%macro i_data (string,file);

ods tagsets.ExcelXP 

options (
sheet name = "&string." /*string name used in macro function*/
sheet_interval = 'proc');

proc print data = input.&file.;
run;

%mend ;

ods tagsets.ExcelXP file="designation/result.xls";

%i_data(ip1, file1);
%i_data(ip2, file2);
%i_data(ip3, file4);

ods tagsets.excelxp close;
 

Consider switching to ODS EXCEL if that's an option, it generates a native XLSX file whereas ODS TAGSETS.EXCELXP generates an XML file that Excel can open. 

[UV_123]

Hi @Reeza,

Thanks for your reply. I have other proc print statements as well in my code. If that's the case, isn't the output of all print statements will be imported to excel? 

I want only the output of a particular proc print statement to be saved in excel and the sheet name should be named after the string I enter in the macro function. 

[Reeza]

Yes all output goes to Excel.

Then you need to explain your full problem, I can only comment on what you posted.

[UV_123]

I want to print only the first output to excel but under different sheets and each sheet should have the name of the string I am inputting in macro. 

%macro i_data (string,file);

ods tagsets.ExcelXP file="designation/result.xls"

options (

sheet name = &string. /*string name used in macro function*/

sheet_interval = 'None'

);

proc print data = input.&file.; 

run;

ods tagsets.ExcelXP close;

proc print data=input.&file.;

where column_1="0";

run;

%mend ;

%i_data(ip1,file1);

%i_data(ip2,file2);

%i_data(ip3,file4);

[ballardw]

Do you want each Proc Print on a different sheet or a group of them?

Regardless, if you want all the output in a single workbook you can only have one ODS excelxp file="<file info".

Period. EACH time you have ODS excelxp file="" you either create a new file OR replace the existing one. So DO not place your ods code inside that particular macro. Period.

[Reeza]

So what do you want to do with the second PROC PRINT? Is it being output to a second file somewhere? 

You cannot close the file and reopen it to write to it again with PROC PRINT/ODS. Perhaps a PROC EXPORT may offer the functionality you prefer otherwise.

[Tom]

Use ODS EXCLUDE and or ODS SELECT to determine which output go to the XLSX file and which don't.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/n0edvjbwu4y1bun1qgmzbk3s3vix.htm#n11g3b...

[Reeza · Accepted solution]

%macro i_data (string,file);

ods tagsets.ExcelXP 

options (
sheet name = "&string." /*string name used in macro function*/
sheet_interval = 'proc');

proc print data = input.&file.;
run;

ods tagsets.excelxp select none;
proc print data=input.&file.;
where column_1="0";
run;
ods tagsets.excelxp select all;

%mend ;

ods tagsets.ExcelXP file="designation/result.xls";

%i_data(ip1, file1);
%i_data(ip2, file2);
%i_data(ip3, file4);

ods tagsets.excelxp close;

Use ODS <destination> SELECT to control what goes where though. I would consider this is an inefficient design as it usually means your piping data to different outputs for different reasons and this will make the code harder to maintain and upkeep in the future. 

[UV_123]

Hi @Reeza,

It did work. Thanks a lot for the solution.