BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

9 REPLIES 9
Reeza
Super User

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);

metallon
Pyrite | Level 9

Hi Reeza!

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

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

metallon
Pyrite | Level 9

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.
metallon
Pyrite | Level 9

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 Smiley Happy

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 Smiley Happy

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

metallon
Pyrite | Level 9

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,...

ballardw
Super User

You have a missing semicolon at

%if &output eq excel

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

and at %else %do

metallon
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1734 views
  • 6 likes
  • 4 in conversation