topic Re: Generate single excel file by calling multiple macros using proc print in ODS and Base Reporting

Generate single excel file by calling multiple macros using proc print

Banu — Fri, 22 Sep 2017 11:36:04 GMT

HI Team,

I have one scenario and looks simple only. I am able to genearte excel file but it's not opening and saying that errors in excel file.

i have code similar like below. file is generating but with errors and not opening.

Please suggest me whetehr below way is correct or not.

%macro ods_rpt(type);

%if &type.= A %then %do;

ods tagsets.excelxp file="/Sales/reports/Audit_Report.xls" style=Printer;

%end;

ods tagsets.excelxp options(embedded_titles='yes'
page_order_across='Yes'
orientation = 'Landscape'
suppress_bylines='Yes'
sheet_name='A'
frozen_headers='yes'
frozen_rowheaders='2'
column_repeat='1-2'
FitToPage='yes'
autofit_height='yes'
sheet_label=' '
);

proc print data=sales&type.;run;

%if &type.= C %then %do;

ods close _all_;

%end;

%if &type.= C %then %do;
x "gzip /Sales/reports/Audit_Report.xls";
x "chmod 644 /Sales/reports/Audit_Report.xls.gz )";
x uuencode /Sales/reports/Audit_Report.xls.gz /Sales/reports/Audit_Report.xls.gz | mailx -s "Report output" xxxxx@xxx.com;
%end;

%mend;

%ods_rpt(A)

%ods_rpt(B)

%ods_rp(C)

Re: Generate single excel file by calling multiple macros using proc print

Cynthia_sas — Fri, 22 Sep 2017 13:01:56 GMT

Hi:
I'm not exactly sure what you're doing in your macro programs, but the statement should be

ODS _ALL_ CLOSE;

and not

ODS CLOSE _ALL_;

as you show in your code.

Next, It would probably be better to redesign your macro. The way I read your macro program, you are making Audit_Report.xls and then immediately zipping it up and doing other things with it in one macro call.

It appears that you are trying to use &type to control the flow of the code that gets generated. I do not see any code for &TYPE=B and you didn't show your SAS log or errors, so it's hard to make constructive suggestions.

cynthia

Re: Generate single excel file by calling multiple macros using proc print

Banu — Fri, 22 Sep 2017 13:27:51 GMT

Thanks for your quick response.

Please see my response below.

1. Yes. Typo mistake. I used ODS _all_ Close;

2. The main purpose of this report is have 3 sas tables salesA,salesB and salesC. I needs to genearte one excel file with 3 sheets.

These 3tables geneartes individually by calling same macro with diff parameters.(Just imazine)

1. %ods_rpt(A) --> when passing A

1. define Excel file name

2. define filebody and sheet name and print data using PRINT statement in sheet name 'A'.

2. %ods_rpt(A) --> when passing B

1. define filebody and sheet name and print data using PRINT statement in sheet name 'B'.(Not defining file name. If defines again file may replace but I need all sheets in single excel file. So I used %if statement to avoid few steps).

3. %ods_rpt(A) --> when passing C

1. define filebody and sheet name and print data using PRINT statement in sheet name 'C'.

2. ODS _all_ Close;

3. Generated excel file making as zip file and sending to the team.

3. No erros in log. Fiel is genearting well and also zipping well.but while opening the excel file getting an error. please the attached file which contains error screen shot.

Please let me know if you have any confusion.

Re: Generate single excel file by calling multiple macros using proc print

ballardw — Fri, 22 Sep 2017 14:43:14 GMT

@Banu wrote:

Thanks for your quick response.

Please see my response below.

1. Yes. Typo mistake. I used ODS _all_ Close;

2. %ods_rpt(A) --> when passing B

1. define filebody and sheet name and print data using PRINT statement in sheet name 'B'.(Not defining file name. If defines again file may replace but I need all sheets in single excel file. So I used %if statement to avoid few steps).

%ods_rpt(A) --> when passing B (besides being poor documentation should be %ods_rpt(B)

Note that you assign the SAME SHEET NAME regardless if A, B or C

ods tagsets.excelxp options(embedded_titles='yes'
page_order_across='Yes'
orientation = 'Landscape'
suppress_bylines='Yes'
sheet_name='A'
frozen_headers='yes'
frozen_rowheaders='2'
column_repeat='1-2'
FitToPage='yes'
autofit_height='yes'
sheet_label=' '
);

Re: Generate single excel file by calling multiple macros using proc print

Reeza — Fri, 22 Sep 2017 14:49:41 GMT

file is generating but with errors and not opening.

What error are you getting?

ODS TAGSETS.EXCELXP does not generate a native XLSX/XLS file, it generates an XML file so you'll get a warning when you open the XML file, but you should still be able to open the file. There is no way around this that I know.

Re: Generate single excel file by calling multiple macros using proc print

Cynthia_sas — Fri, 22 Sep 2017 19:08:20 GMT

Hi:

I am not sure why you are getting that error. It is generally the type of error you get when you violate some Spreadsheet Markup Language rule that Microsoft set.

I made a slightly different version of your program, using SASHELP datasets and changed the macro program to use keyword parameters because I don't like positional parameters and I cleaned up the %IF statements and used &TYPE for the sheet_name and the Excel file opened correctly for me.

Here's the code I tried. Note that I used the correct file extension of .XML for this output (instead of .XLS). I used the TAGSETS.EXCELXP version:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015). Add
options(doc='help') to the ods statement for more information.

And I ran this on a SAS 9.4 M3 system on Windows.

cynthia

If you try my code and get the same Workbook error on open, then I recommend that you open a track with Tech Support.

%macro ods_rpt(type=);

%if &type.= A %then %do;

%let want=sashelp.class;

ods tagsets.excelxp file="c:\temp\Test_Audit_Report.xml"  style=Printer;

%end;
%else %if &type = B %then %do;
  %let want=sashelp.shoes;
%end;
%else %if &type = C %then %do;
  %let want=sashelp.heart;
%end;

ods tagsets.excelxp options(embedded_titles='yes'
page_order_across='Yes'
orientation = 'Landscape'
suppress_bylines='Yes'
sheet_name="&type"
frozen_headers='yes'
frozen_rowheaders='2'
column_repeat='1-2'
FitToPage='yes'
autofit_height='yes'
sheet_label=' '
);

proc print data=&want (obs=3);
title "Type: &type is reporting on &want";
run;
title;

 

%if &type.= C %then %do;

ods _all_ close ;

   %put Begin==========> TYPE value is &type last dataset is &want;
   %put I do not issue unix commands on Windows;
   %put So this is something in the log;
   %put instead of the original unix commands;
   %put End ==========>;
%end;

%mend ods_rpt;

%ods_rpt(type=A)

%ods_rpt(type=B)

%ods_rpt(type=C)

Re: Generate single excel file by calling multiple macros using proc print

Tom — Sat, 23 Sep 2017 01:58:41 GMT

@Cynthia_sas wrote:

...
... and changed the macro program to use keyword parameters because I don't like positional parameters ...

@Cynthia_sas

One of the nicer features of SAS macros is that you can call a macro using named parameters even for parameters that were defined as positional. So there is no need to modify the macro definition to use the names in the macro call.

%macro transform(in,out);
....
%mend transform ;

%transform(out=want,in=have);

Re: Generate single excel file by calling multiple macros using proc print

Tom — Sat, 23 Sep 2017 02:18:36 GMT

Does it work if you transfer the file in some other way and test it?

Does it work if you delay running the gzip/email step?

I find it helps a lot to use a data step with an INFILE statement with PIPE option to run OS commands so that any messages are returned to the data step. I also noticed that you did not properly quote the subject line in your mailx command because of nested quotes.

%macro ods_rpt(type);
%local fname email;
%let fname=/Sales/reports/Audit_Report.xls ;
%let email=xxxxx@xxx.com;

%if &type.= A %then %do;
ods tagsets.excelxp file="&fname"  style=Printer
options
(embedded_titles='yes'
 page_order_across='Yes'
 orientation = 'Landscape'
 suppress_bylines='Yes'
 frozen_headers='yes'
 frozen_rowheaders='2'
 column_repeat='1-2' 
 FitToPage='yes'
 autofit_height='yes'
 sheet_label=' '
);
%end;

ods tagsets.excelxp options (sheet_name="&type");

proc print data=sales&type.;
run;
 
%if &type.= C %then %do;
ods  tagsets.excelxp _all_;

data _null_;
  infile 
"gzip &fname
;chmod 644 &fname..gz 
;uuencode &fname..gz &fname..gz | mailx -s ""Report output"" &email
" pipe;
  input;
  put _infile_;
run;
%end;
%mend ods_rpt;

Re: Generate single excel file by calling multiple macros using proc print

Cynthia_sas — Sat, 23 Sep 2017 12:38:56 GMT

Tom, it is true, I could have defined them one way but specified them another to invoke. But I prefer to be consistent and not mix the methods. I find keyword parameters easier to explain in both definition and invocation to new macro users. It's a preference, not the only way. That is the beauty of the Macro Facility -- so much flexibility.

cynthia