topic Re: Calling macro twice wont work, while calling it once works fine in ODS and Base Reporting

Calling macro twice wont work, while calling it once works fine

HussamAlSaffar — Thu, 12 Dec 2019 17:13:43 GMT

INFO:

SAS EG 7.15

Server-setup from a workplace

Hi Guys! Quite new to sas here.

I've managed to write a macro that exports already produced datasets, which works perfectly when I call the macro the first time. Second time I call the macro, it seems to ignore the first call, write out an excel-file with the correct name (the name for the second call) but the sheets and data are from the first call.

I'm doing this for six datasets in this case. So when I call the macro 6 times, very oddly, the macro again skips first call, writes out second call with first call data, writes out third call correctly with third call data, fourth call with third call data, fifth call with fourth call data and sixth with fifth. So NOT in a specific pattern as I see it.

I want to add, that the macro works perfectly when it is called ONCE no matter which call I make.

Any ideas?

%macro ultimate(file, sheet1, sheet2, sheet3, data1, data2, data3);
ods excel file="<*FILEPATH*>/&file..xlsx"  style=meadow
        options(
				sheet_interval='bygroup'
				frozen_headers='1'
				);
ods excel options(sheet_name=&sheet1);
PROC print
data=&data1 noobs;
id Kontonr Type Tjenestenr Navn;
sum '01'n '02'n '03'n '04'n '05'n '06'n '07'n '08'n '09'n '10'n '11'n '12'n 'Forventet_Lønudgift'n;
run;
ods excel options(sheet_name=&sheet2);
PROC print
data=&data2 noobs;
id Kontonr Type Tjenestenr Navn lonart_tekst;
sum '01'n '02'n '03'n '04'n '05'n '06'n '07'n '08'n '09'n '10'n '11'n '12'n 'Forventet_udgift'n;
run;
ods excel options(sheet_name=&sheet3);
PROC print
data=&data3 noobs;
id aar Tjenestenr Navn fravaersaarsag dato_fravaer_start dato_fravaer_slut;
run;
%mend ultimate;

%ultimate(JU1, 'JU1', 'JU1 Lønart', 'JU1 Fravær', work.ju1, work.ju1_loenart, work.'fravær_ju1'n);

%ultimate(JU2, 'JU2', 'JU2 Lønart', 'JU2 Fravær', work.ju2, work.ju2_loenart, work.'fravær_ju2'n);

The error im getting is:

ERROR: Physical file does not exist

and

ERROR: No body file. EXCEL output will not be created

(Which is btw a LIE because it does create excel outputs, just in a weird and wrong way!)

and

ERROR: Insufficient authorization to access /sas/prod/config_18W04/Lev1/EGApp/sasexcl.xlsx.

Disclaimer, I'm sure you guys will have a thousand ways to optimize the above, those are welcome if you really can't help it 😄

Thank you so much in advance!

Best

Eager new sas-user

Hussam

PS. Hope I managed to bring in all the relevant info on my first post on here

Re: Calling macro twice wont work, while calling it once works fine

Reeza — Thu, 12 Dec 2019 17:21:23 GMT

You don't have an ODS EXCEL CLOSE that I can see, which you need to close the file and finish the processing of the Excel file.

I'm guessing your file path is incorrect as well.

In general, when writing macro code you should have working code that first works and then you convert it to a macro. Do you have base working code first?

Here's some macro references that may help you out.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@HussamAlSaffar wrote:

INFO:

SAS EG 7.15

Server-setup from a workplace

Hi Guys! Quite new to sas here.

I've managed to write a macro that exports already produced datasets, which works perfectly when I call the macro the first time. Second time I call the macro, it seems to ignore the first call, write out an excel-file with the correct name (the name for the second call) but the sheets and data are from the first call.

I'm doing this for six datasets in this case. So when I call the macro 6 times, very oddly, the macro again skips first call, writes out second call with first call data, writes out third call correctly with third call data, fourth call with third call data, fifth call with fourth call data and sixth with fifth. So NOT in a specific pattern as I see it.

I want to add, that the macro works perfectly when it is called ONCE no matter which call I make.

Any ideas?
%macro ultimate(file, sheet1, sheet2, sheet3, data1, data2, data3);
ods excel file="<*FILEPATH*>/&file..xlsx"  style=meadow
        options(
				sheet_interval='bygroup'
				frozen_headers='1'
				);
ods excel options(sheet_name=&sheet1);
PROC print
data=&data1 noobs;
id Kontonr Type Tjenestenr Navn;
sum '01'n '02'n '03'n '04'n '05'n '06'n '07'n '08'n '09'n '10'n '11'n '12'n 'Forventet_Lønudgift'n;
run;
ods excel options(sheet_name=&sheet2);
PROC print
data=&data2 noobs;
id Kontonr Type Tjenestenr Navn lonart_tekst;
sum '01'n '02'n '03'n '04'n '05'n '06'n '07'n '08'n '09'n '10'n '11'n '12'n 'Forventet_udgift'n;
run;
ods excel options(sheet_name=&sheet3);
PROC print
data=&data3 noobs;
id aar Tjenestenr Navn fravaersaarsag dato_fravaer_start dato_fravaer_slut;
run;
%mend ultimate;

%ultimate(JU1, 'JU1', 'JU1 Lønart', 'JU1 Fravær', work.ju1, work.ju1_loenart, work.'fravær_ju1'n);

%ultimate(JU2, 'JU2', 'JU2 Lønart', 'JU2 Fravær', work.ju2, work.ju2_loenart, work.'fravær_ju2'n);
The error im getting is:

ERROR: Physical file does not exist

and

ERROR: No body file. EXCEL output will not be created

(Which is btw a LIE because it does create excel outputs, just in a weird and wrong way!)

and

ERROR: Insufficient authorization to access /sas/prod/config_18W04/Lev1/EGApp/sasexcl.xlsx.

Disclaimer, I'm sure you guys will have a thousand ways to optimize the above, those are welcome if you really can't help it 😄

Thank you so much in advance!

Best

Eager new sas-user

Hussam

PS. Hope I managed to bring in all the relevant info on my first post on here

Re: Calling macro twice wont work, while calling it once works fine

HussamAlSaffar — Thu, 12 Dec 2019 17:33:41 GMT

Awh man it was the ODS EXCEL CLOSE All along! So silly. Thanks a lot!

Btw. I did create a working program before trying to expand it, so I completely agree that that is the way to go about creating any program.

Thanks again!!

Re: Calling macro twice wont work, while calling it once works fine

PaigeMiller — Thu, 12 Dec 2019 17:34:15 GMT

I think @Reeza is right about not having and ODS EXCEL CLOSE; statement.

But this particular error is not a macro issue

When I run this code twice (note: I deliberately did not include ODS EXCEL CLOSE;)

ods excel file="test.xlsx";
proc print data=sashelp.class;
run;

I get this error on the second run of the code.

ERROR: No body file. EXCEL output will not be created.

Re: Calling macro twice wont work, while calling it once works fine

Reeza — Thu, 12 Dec 2019 17:36:18 GMT

I think ODS has change the defaults, it should be BODY= not FILE= in the newest versions of SAS in the ODS statement.