How to export to Excel based on a parameter

[deleted_user]

I'm trying to export from a stored process to an Excel file, using a macro I wrote:

%macro OutToExcel;
%Proc EXPORT Data = whatever
%OUTFILE="c:\sas\data\restrictedgifts.xls"
%DBMS=excel2000
%REPLACE;
%RUN;
%mend OutToExcel;

However, when I call to the macro from the following code,

data checkexcel;

if ( UPCASE("&title")='NO') then do;
%OutToExcel;
end;
run;

I get the following in my log output::

286 +if ( UPCASE("&title")='NO') then do;
287 + %OutToExcel;
NOTE: Line generated by the invoked macro "OUTTOEXCEL".
287 %Proc EXPORT Data = whatever %OUTFILE="c:\sas\data\restrictedgifts.xls" %DBMS=excel2000
_
180
287 ! %REPLACE; %RUN;
WARNING: Apparent invocation of macro PROC not resolved.
WARNING: Apparent invocation of macro OUTFILE not resolved.
WARNING: Apparent invocation of macro DBMS not resolved.
WARNING: Apparent invocation of macro REPLACE not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

Can I not include a Proc in side other code? What might be a correct way to be able to use the Proc Export from within parameter-checking code? Or, are there alternatives for trying to automatically export to Excel?

[Cynthia_sas]

Hi!
First, the way your macro is defined is causing you a problem. It should be more like this:
[pre]
%macro OutToExcel;
Proc EXPORT Data = whatever
OUTFILE="c:\sas\data\restrictedgifts.xls"
DBMS=excel2000
REPLACE;
RUN;
%mend OutToExcel;
[/pre]

However, you WILL still have problems because you'd need to invoke this from a CALL EXECUTE from within your data set.

Even if you fixed both of those things, you still have a third problem because you are making an incorrect assumption about SAS stored processes. You are making the assumption that every time the macro is run that you will be creating a file on the local system (c:\sas\data\restrictedgifts.xls ...but this is NOT correct .... you would be running the stored process on the workspace server or the stored process server and the results will come back to whichever client is open when they run the stored process. Unless you make a permanent package file, you cannot control the name of the results that get sent back to the BI client application. This stored process would NOT work in the Info Delivery Portal or in Web Report Studio, for example.

I would be tempted to write a stored process that could be executed from within the SAS Add-in for Microsoft Office this way (assuming that &title is a parameter coming from the stored process interface):
[pre]

%macro cktitle;
%if %upcase(&title) = NO %then %do;
proc print data=whatever;
run;
%end;
%else do;
%put ======> Checking TITLE parameter:
%put ======> To run this in Excel, select NO for the TITLE prompt;
%end;
%mend cktitle;

%global TITLE;
*ProcessBody;
%stpbegin;
%cktitle;
%stpend;
[/pre]

Then I would register the stored process to run on the server and return streaming or transient results. If the user submits this from EG, for example, they would create an HTML result by default & they could do an export to excel from EG or they could use the SAS Add-in for Microsoft Office in Excel and they could then execute the stored process and save the results to whatever file name they wanted on their local system.

If you registered this stored process as creating a permanent package file, then you could control where the package was saved and what to name it. BUT, the package would not contain an Excel file unless you did an explicit publish of the Excel file to the package.

For more help with your stored process, I recommend contacting Tech Support so they can follow through with you on exactly what it is you want to do, which client applications need to execute the stored process and how to best accomplish what you want to do.

cynthia

[deleted_user]

Hi, Cynthia,

Wow, that sounds like a ton of effort for a relatively small return..... I guess if there isn't an easier way, then I'll have the users go in through Excel via the SAS Office Add-in. I was trying to save the users an extra step, but....oh well......

Thanks for replying!

[Cynthia_sas]

Hi!
Absolutely right. I would only do a stored process to send something to Excel, if for some reason, I wanted the results of some procedure in Excel -- for example -- I had a summary report done with PROC TABULATE and the users wanted to see that table and do more with it. Or I wanted to surface a PROC REG set of numbers in Excel -- something along those lines -- or maybe I wanted to give them a parameter that would narrow down the rows or columns that they were going to see. But, to just see the data in Excel, I'd probably teach them to use the Add-in -- you don't actually need a stored process for that at all.

I thought you WANTED a stored process to surface results -- the SAS Add-in is a much better solution for a simple data access in Excel.

cynthia