topic Re: autocall macro to let user choose to export as excel/pdf in ODS and Base Reporting

autocall macro to let user choose to export as excel/pdf

metallon — Wed, 01 Apr 2015 14:59:46 GMT

Hi SAS Experts,

I am looking for a autocall macro that I can include in my program´s for giving the user

the chance to export the data set as PDF or Excel/CSV

Ideally I want to have styling for both.

I imagine the parameters handed over to the macro must be something like

1. data set name (its hanging around in the work library) and at least the 2. csv file name (a mix of report title and date).

I imagine this is functionality that has been implemented a million times,.. yet I dont find a code or macro library

on the web that I can just re-use but instead tons of snippeds that present some ODS functions for which I need

to spend days to extend (if possible at all).

Has any of you came accross such a macro?

Thanks for any help!

Re: autocall macro to let user choose to export as excel/pdf

Reeza — Wed, 01 Apr 2015 16:35:00 GMT

I think ODS is the correct approach and this would be a straightforward macro, until you choose to valid all the macro parameters and add options to your reports.

Here's an untested example:

%macro print_report(dataset=, file_name=, type=);

ods &type file="C:\temp\&file_name" style=meadow;

proc print data=&dataset;

run;

ods &type close;

%mend print_report;

%print_report(dataset=sashelp.class, file_name=class_20150401, type=PDF);

Re: autocall macro to let user choose to export as excel/pdf

metallon — Thu, 02 Apr 2015 09:20:16 GMT

Hi Reeza!

Thanks for that! Awesome. I tried it and it works almost

I noticed that the file name does not have a dot between file name and file type. I tried it with catx but somehow that did not work.

Do you have a suggestion how to get the .dot between file name and type?

%macro x_output(dataset=, file_name=, type=);

typenew=catx('. ',&type);

ERROR 180-322: Statement is not valid or it is used out of proper order.

ods &type file="R:\NR\Scripts\13_SAS\&file_name &typenew" cssstyle="R:\NR\Scripts\13_SAS\sas_marko\style_v01.css";

proc print data=&dataset;

run;

ods &type close;

%mendx_output;

Bye

Phil

Re: autocall macro to let user choose to export as excel/pdf

metallon — Tue, 07 Apr 2015 08:01:53 GMT

got it %LET typenew=catx('. ',&type);

but the export does not work as MS Excel:

%macro print_report(dataset=, file_name=, type=);

ods &type file="C:\temp\&file_name" style=meadow;

proc print data=&dataset;

ru;

ods &type close;

%mend print_report;

%print_report(dataset=sashelp.class, file_name=class_20150401, type=XLS);

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: autocall macro to let user choose to export as excel/pdf

RW9 — Tue, 07 Apr 2015 08:24:39 GMT

Hi,

You haven't posted the relevant log part, so we can't see what code is being generated, however I would suggest your problem is that "type=XLS" where XLS is not an ods destination. You could put HTML or CSV or tagsets.excelxp.

I also don't see what value you are gaining from this. The macro language isn't there to replace the need for base coding. Excel files and PDF files are strcuturally different outputs, PDF is paged, document structure, Excel is a mess of anything and everything. Are you going to attempt to handle every possible convolution of differences between the two? For one output destination, if the data/output layout is more or less the same then there may be an argument for creating a generic, if not your just going to give yourself lots of problems.

Finally, you could also think about how operation is going to happen. For instance, do you *need* to put the ods statements into the macro as that is limiting the functionality. If the user puts the ods statements before and after the macro call, and you just create a print macro, then you don't need to worry about that part, they can setup any of the detsinations. I.e. when someone comes back next week and says, I want RTF output, you then need to go through the lifecycle management of a standard macro to alter it to add this.

Re: autocall macro to let user choose to export as excel/pdf

metallon — Tue, 07 Apr 2015 09:48:08 GMT

Hi RW9,

You are right. Its HTML with a .xls file ending that is used.

The ultimate goal is to offer the Stored Process Web Application user

a html link that says "export as pdf" or "export as excel" or "export as html" and

then prompts for a destination to save on the desktop. Why? because its a user requirement to

play around with the data, keep it, or print it. I have to live with it.

Handling convolution is not the aim.

the "user" is not going to use my macro´s. it will be only myself to develop STP´s but right now

the SAS Sever isnt running so I do my "pre-work" without the STP Server

Below is what I got so far. I feel like I am reinventing the wheel. Stuff like this must have been done a million times before

In the macro part I am missing an IF/ELSE that not only gives me the opportunity to apply different styles but also

options that deal with excel-export problems like cutting of leading 0.

options nodate pageno=1 linesize=80 pagesize=40 orientation=landscape;

%include "R:\....sas_generate_output.sas" ;

%LET RepTitel = myreport;

%LET RepFileName = NRKP_Positive_Einzel_v01;

%LET RepFileNameExcel=&RepFileName'.xls';

%LET RepFileNameHTML=&RepFileName'.html';

%LET RepFileNamePDF=&RepFileName'.pdf';

%LET RepKopfZeile = x;

%LET RepFussZeile = y;

%LET RepName = &SYSDATE ;

%LET RepVersion = z;

%LET RepDB = w;

proc sql;

connect to oracle as nrkpdb (user=xxx password=yyyy path=ccc);

create table positive as

(

select *

from connection to nrkpdb

(

select mydata from xxxx

)

);

disconnect from nrkpdb;

quit;

%let dsid=%sysfunc(open(positivliste));

%let num=%sysfunc(attrn(&dsid,nlobs));

%let rc=%sysfunc(close(&dsid));

proc report data=positive;

title &RepTitel;

compute after ;

text = "&sysuserid: &RepFileName

%sysfunc(datetime(),datetime.) &num. Results" ;

line @1 text $108.0;

endcomp;

run;

ods _all_ close;

/*Generate output files*/

%sas_generate_output(dataset=positivliste, file_name=&RepFileNamePDF, type=pdf);

%sas_generate_output(dataset=positivliste, file_name=&RepFileNameHTML, type=html);

%sas_generate_output(dataset=positivliste, file_name=&RepFileNameExcel, type=html);

%macro sas_generate_output(dataset=, file_name=, type=);

proc template;

/*HTML Style*/

define style styles.mycssstylescreen;

import ".....sas_style_v01.css" screen;

end;

/*PDF Style*/

define style styles.mycssstyleprinter;

parent=styles.mycssstyle;

import "....sas_style_v01.css" print;

end;

run;

/*Text Format applied to all columns HEADTEXT= “<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>”;*/

ods &type file="sas_output\&file_name" cssstyle="sas_style_v01.css";

proc print data=&dataset;

run;

ods &type close;

%mend sas_generate_output;

Re: autocall macro to let user choose to export as excel/pdf

RW9 — Tue, 07 Apr 2015 09:53:41 GMT

Oh, so its a graphic front end. Why not use Visual Analytics or Web Report Studio to do this type of thing. They are built for that.

Re: autocall macro to let user choose to export as excel/pdf

metallon — Tue, 07 Apr 2015 14:43:40 GMT

Hi RW9. Right now we have no Web Report Studio. It might be coming in a few months. but nevertheless the requirement is to have an easy way to get the output shows in WRS to MS excel. I tried to enhance the macro to use different styling depening on ms excel or pdf but so far I am not lucky with the IF clause:

%macro sas_generate_output(dataset=, file_name=, output=, type=);

proc template;

/*HTML Style*/

define style styles.mycssstylescreen;

import "...sas_marko\sas_style_v01.css" screen;

end;

/*PDF Style*/

define style styles.mycssstyleprinter;

	parent=styles.mycssstyle;
	import "....sas_style_v01.css" print;

end;

run;

%if &output eq excel

%then %do

	/Text Format applied to all columns HEADTEXT= “<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>”;/
	ods &type file="C:\temp\&file_name" style=styles.mycssstyleprinter
	%end;
%else	%do
	ods &type file="C:\temp\&file_name" style=styles.mycssstylescreen
	%end;

/*cssstyle="....sas_marko\sas_style_v01.css";*/

proc print data=&dataset noobs;

run;

ods &type close;

%mend sas_generate_output;

Expected %TO not found in %DO statement.

If I do it without the DO the error is:

ERROR 22-322: Syntax error, expecting one of the following: ;, ANCHOR, AUTHOR,

BACKGROUND, BASE, BODY, BOOKMARK, BOOKMARKGEN, BOOKMARKLIST,

BOX_SIZING, CLOSE, COLOR, COLUMNS, COMPRESS, CONTENTS, CSSSTYLE,...

Re: autocall macro to let user choose to export as excel/pdf

ballardw — Tue, 07 Apr 2015 15:26:24 GMT

You have a missing semicolon at

%if &output eq excel

%then %do <I think this is where you need ;>

and at %else %do

Re: autocall macro to let user choose to export as excel/pdf

metallon — Wed, 08 Apr 2015 07:36:56 GMT

that worked:

%if &output eq excel %then %do;

ods &type file="C:\temp\&file_name" style=styles.mycssstyleprinter;

%end;

%else %do;

ods &type file="C:\temp\&file_name" style=styles.mycssstylescreen;

%end;