Date:         Mon, 11 Jan 1999 08:01:17 -0800
Reply-To:     "Lanning, Chris" <clanning@AMGEN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Lanning, Chris" <clanning@AMGEN.COM>
Subject:      Re: Export SAS data set directly to .xls file
Comments: To: Douglas Small <dsmall@BPFL.COM>
Content-Type: text/plain

Hello Douglas,
 
Here is the macro I use ...
 
***  Macro exprt:  exports SAS dataset to an excel file.  The arguments are:
                data - name of the SAS data set to export
                file - path and name of new Excel file.;
 
%macro exprt(data, file);
options noxwait;
 
%sysexec del &file;  /*  WARNING: This will delete any other instances of
the
                        file you are exporting to */
 
PROC DBLOAD DBMS=EXCEL DATA=WORK.&data;
PATH="&file";
PUTNAMES YES;
LIMIT=0;
LOAD;
 
 
RUN;
 
%mend exprt;
 
data temp;
        do var1 = 1 to 10;
                var2 = ranuni(-1);
                output;
        end;
 
run;
 
%exprt(temp, c:\Mydocu~1\test2.xls);
 
run;
 
I am basically just using the code that is used when you run the export
wizard in SAS except I put it into a macro.
 
Hope it helps.
 
Chris Lanning
Trilogy Consultant
clanning@amgen.com
1-805-447-0634
SAS Consulting is my life!!!
 
> ----------
> From:         Douglas Small[SMTP:dsmall@BPFL.COM]
> Reply To:     Douglas Small
> Sent:         Friday, January 08, 1999 1:13 PM
> To:   SAS-L@UGA.CC.UGA.EDU
> Subject:      Export SAS data set directly to .xls file
>
> I've been trying to Export a SAS data set to an .xls file (Excel 97
> format) without involving Excel at all.
>
> I have tried putting SAS variables into an Excel spread sheet using
> _null_ data statements with 'Excel|System' commands, but find this more
> difficult than manually exporting with the Export wizard in SAS.  I have
> also tried copying the SAS code created when exporting a SAS file to
> .csv (comma delimited) with the export wizard, but this code is rather
> cumbersome and difficult to apply to different data sets and I still
> have to import it into excel.
>
> Does anybody know of any efficient methods of exporting an entire SAS
> data set to an .xls file?  Your help is appreciated.
>
> Thanks,
> Doug
>
>
>
> --
> ----------------------------------------
> Douglas Small - Credit Policy
> Em: dsmall@bpfl.com  Ph: 407.370.7688
>
> Banco Popular, N.A. (Florida)
> U.S. Card Division
> 5551 Vanguard St., Suite 200
> Orlando, FL 32819
> ----------------------------------------
>