Generate a new XLSX file for each iteration in a macro with ODS EXCEL

[commitsudoku]

Hi,

I am trying to generate an Excel report for each month of a given year. 

So, the following macro generates a new variable, MMOIS to chose a month and uses that variable to generate a PROC TABULATE for that month; so in this simplified example, a report is created for the first three monthes of the year (JAN, FEB and MAR).

%macro comp;
%local i ;

	/*For JAN, FEB and MAR*/
	%do i=1 %to 3;
	
	data temp; set temp ;
	IF (MONTH = &i	AND	ANNEE=&this_year)		THEN 	MMOIS='Curr' ;
	IF (MONTH = &i	AND	ANNEE=&last_year)		THEN 	MMOIS='Prev' ;
	run;

        ods excel file="\\DIRECTORY\tabulate_i.xlsx" options (sheet_interval='PROC');

	PROC TABULATE DATA = temp out=secteurs_apen;
	CLASS region mmois / MLF  ;  
	TABLE (region=''), (mmois='')    ; 
	run;

	data temp; set temp ;
	DROP MMOIS;
	run;

ods excel close;

	%end;
%mend comp;
%comp

The problem arises in the following part : ods excel file="\\DIRECTORY\tabulate_i.xlsx" 

This command generates 3 corrupt files names  tabulate_1xlsx, tabulate_2xlsx and tabulate_3xlsx. But what I really want is to have 3 Excel files like "tabulate_1.xlsx". I have even tried to generate using CAT as in :

ods excel file=cat("\\DIRECTORY\tabulate",&i,".xlsx")

...but it didn't work. 

So, is there a way to generate an XLSX file for each iteration using ODS EXCEL ?

How can I add ".xlsx" after the "&i"? 

Thank you very much,

[ballardw]

"Didn't work" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

The problem arises in the following part : ods excel file="\\DIRECTORY\tabulate_i.xlsx" 

This command generates 3 corrupt files names  tabulate_1xlsx, tabulate_2xlsx and tabulate_3xlsx. But what I really want is to have 3 Excel files like "tabulate_1.xlsx". I have even tried to generate using CAT as in :

No it doesn't. That ods excel file= will only create Tabulate_i.xlsx file (over and over again) because you have not included the macro parameter &i. The macro language uses the . to indicate the end of macro value so you need an extra . along with the macro &

ods excel file="\\DIRECTORY\tabulate_&i..xlsx" 

The rationale is that without the first dot then &ixlsx would look for a macro variable named ixlsx. So &i.xlsx tells SAS "use the value of &i then add the xlsx afterward". But you want the name to end in .xlsx so requires one more . in the name.

Since you constantly overwriting your data set TEMP are you sure that the contents at any give step are even correct for any given output.

BTW it is very bad form to have a macro variable like &this_year just appear in the  middle of code when it is not a parameter to the macro. You can have very odd things happen because this macro doesn't control where the value comes from.

[commitsudoku]

Yes, I had put the ampersand before the i; I just forgot it in my post. This is exactly what I needed though ; "tabulate_&i..xlsx".
I just didn't know I had to put two dots. It now works like a charm, thank you !
As for the "it didn't work", like I said ; SAS generated 3 corrupt files that I couldn't open with any software.

[Reeza]

They aren't corrupt, they have no extension. If you add .xlsx to the end they'll likely open.

[ballardw]

---

@commitsudoku wrote:
Yes, I had put the ampersand before the i; I just forgot it in my post. This is exactly what I needed though ; "tabulate_&i..xlsx".
I just didn't know I had to put two dots. It now works like a charm, thank you !
As for the "it didn't work", like I said ; SAS generated 3 corrupt files that I couldn't open with any software.

---

That "corrupted" is something reported OUTSIDE of SAS, not in SAS.

I will be that if you still have one of those files around and rename the file so that it has the dot before xlsx that the file opens just fine.

Unfortunately the likely operating system without a the clear delineation of the EXTENSION in the file name does not know what program to even attempt to open the file by default so may do that. Excel and other will also look at extensions and make assumptions about file type and generate different messages depending on environment.

[svh · Accepted solution]

In your original line of code that begins with ODS Excel, have you tried putting the ampersand before the i variable and adding an extra period after it? The extra period tells SAS when the macro variable name ends, which allows the other period to be used to say where the file extension begins. It would look like this:

"\\DIRECTORY\tabulate_&i..xlsx"

[commitsudoku]

Thank you so much; I had put the ampersand (it was a mistake in my post but not in the code) but I didn't know I had to put two dots before the "xlsx". It works like a charm now. Thank you very much.