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
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-Ap...
@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
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-Ap...
@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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.