Export to Excel and create a report

[dbdinc]

In Enterprise Guide, from within a stored process, is it possible to both export to Excel and to create a report? My stored process code appears to run successfully, but all I get is a report, and no excel file in my specified folder. Here is the piece of code that I am trying to execute:


data reset;

if ( UPCASE("&title")='YES') then do;
if _n eq 1 then
flag = 0;
else flag = flag + 1;
if flag >= 11 then do;
pageit + 1;
flag = 0;
end;
set sortfile;
end;
else set sortfile;
run;

%macro OutToExcel;
%if ( "&title"='NO') %then %do;
Proc EXPORT Data = sortfile;
OUTFILE="c:\SAS\data\restrictedgifts.xls";
DBMS=excel2000;
REPLACE;
RUN;
%end;
%mend OutToExcel;

%OutToExcel;
RUN;

/*****************************************************************************************/
/* The next lines of code "set up" the report headers to be printed. Depending */
/* on whether the data is to be exported to Excel, the headers will either */
/* contain data or be blank. */
/*****************************************************************************************/

TITLE;
TITLE1 &title1;
TITLE2 &title2;
TITLE3 &title3;

FOOTNOTE;
FOOTNOTE1 &footnote1;
FOOTNOTE2 &footnote2;

OPTIONS noBYLINE;
OPTIONS orientation=landscape;

proc report data=sortfile spacing=1 style(Header)={font_size=1.0 font_weight=medium} split='*' missing;

/*****************************************************************************************/
/* The following code prints the data in a report format */
/*****************************************************************************************/

COLUMN ORGANIZATION_DESC FUND ORGANIZATION_CODE ACCOUNT PROGRAM ACTIVITY ACCOUNT_POOL ACCOUNT_TYPE_LEVEL_1
ACCOUNT_TYPE_DESC_1 PROGRAM_DESC ATTRIBUTE_TYPE ACCOUNT_DESC SUM_ADOPTED_BUDGET
SUM_BUDGET_ADJUSTMENTS SUM_ACCUMULATED_BUDGET SUM_BUDGET_RESERVATION SUM_ENCUMBRANCES
CURR_YEAR_TO_DATE_ACTIVITY SUM_YEAR_TO_DATE_ACTIVTY SUM_REMAINING_BALANCE;

DEFINE ORGANIZATION_DESC / DISPLAY "ORG DESC" style=[just=l font_weight=demi_bold font_size=1.0];
DEFINE FUND / DISPLAY "FUND" style=[just=l font_weight=demi_bold font_size=1.0];
DEFINE ORGANIZATION_CODE / DISPLAY "ORG CODE" style=[just=l font_weight=demi_bold font_size=1.0];
DEFINE ACCOUNT / DISPLAY "ACCT" style=[just=l font_weight=demi_bold font_size=1.0];
DEFINE PROGRAM / DISPLAY "PGM" style=[just=l font_weight=demi_bold font_size=1.0];
DEFINE ACTIVITY / DISPLAY "ACTIVITY" style=[just=l font_weight=demi_bold font_size=1.0];

A sincere thank you to all who are willing to help eliminate my headache.

 

[ChrisW75]

Hi dbdinc, I found a couple of what I think are errors in your code, try this (just paste it into a SAS window and submit it:-

%let title = NO;
%macro OutToExcel;
    %if ( "&title"="NO") %then %do;
        Proc EXPORT Data = sashelp.class
            OUTFILE="c:\restrictedgifts.xls"
            DBMS=excel2000
            REPLACE;
         RUN;
    %end;
%mend OutToExcel;

%OutToExcel;

I couldn't fully test it because I don't have SAS/Access for PC Files. However
error1 - Double quotes around &title but singles around NO. The problem is, in macro processing, the quotes are considered part of the literal string, so it is actually checking to see if "NO" = 'NO' which it doesn't.
error2 - semi colons at the end of each line, Proc to Replace is all one statement.
Try that and see how it goes...

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[dbdinc]

Chris,

Thank you very much!! I just thought of another question about my plight.... We are using servers for our SAS processing - will what I have put my output Excel file onto the server? That's not what I want. I want the output Excel file to go to my own computer's C; drive. Do you know how I can accomplish that?

Thank you so much for helping me this far!!

 

[ChrisW75]

Hmmm. Well, the problem is that SAS on your server doesn't know where your computer is and what it is called. You'd need to have a link set up on the server pointing to your computer (like a shared network drive).
Another method is to use E-mail. SAS can write out to an e-mail address, and it's pretty well documented. Basically, if e-mail is set up correctly (and if it isn't go talk to your admins and ask them nicely to set it up) all you need to do is create a filename statement with the e-mail address and assorted things like subject line and attachments (the important bit, because you'll want to attach your Excel spreadsheet to it). Then you just write a data _null_ step, file to the e-mail filename, and put in a brief message to accompany the file. I've used that a few times, and it's a pretty handy trick.
Lookup e-mail here:-

http://support.sas.com/onlinedoc/913/docMainpage.jsp

I'm away for the next 2 weeks, if you need further help, hopefully someone else will be able to.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[dbdinc]

Sounds interesting..... I'll check into it, and thanks one more time!

 

[dbdinc]

Chris.....

That's a LOT of documentation! Where in there would I find info about your suggestion??