SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

export to excel

Reply
Occasional Contributor CG1
Occasional Contributor
Posts: 13

export to excel

Hi,

 

I am new in SAS, I executed one proc sql in SAS editor. below is the query

 

libname db oracle user=GDC password=GDC123 path=prod schema=SALES;

proc sql;
select sales_status,payment_status,datepart(last_order_dt) format=ddmmyy10.,individual_id, (date()-10) format=ddmmyy10.
from db.print_profile where individual_id=1045582 and datepart(last_order_dt)='18-dec-2014'd ;
quit; 

 

using above statement I am getting 80 rows in SAS editor output window.

 

Can anyone please suggest me how to export this output in Excel from SAS editor output window.

 

Super User
Super User
Posts: 7,721

Re: export to excel

Hi,

 

There are a number of methods to get data from SAS into Excel.  I would recommend creating a dataset from your SQL first however.

libname db oracle user=GDC password=GDC123 path=prod schema=SALES;
proc sql;
  create table WORK.RESULTS as
  select  SALES_STATUS
          ,PAYMENT_STATUS
          ,datepart(LAST_ORDER_DT) format=ddmmyy10.
          ,INDIVIDUAL_ID, 
          (date()-10) format=ddmmyy10.
  from    DB.PRINT_PROFILE 
  where   INDIVIDUAL_ID=1045582 
    and   datepart(LAST_ORDER_DT)='18-dec-2014'd ;
quit; 

ods tagsets.excelxp file="c:\example1.xml";
proc report data=work.results nownd; 
  columns _all_;
run;
ods tagsets.excelxp close;

proc export data=work.results outfile="c:\example2.xlsx";
run;

The above shows 2 examples of how to get data out.  The second I tend to avoid using however, proc import/export are guessing procedures, so you may not get what you expect.  With the tagsets, its not actually creating an Excel file, but XML which Excel can interpret.  This option allows you to specify formatting, styles, Excel options etc. and so is far more flexible than proc export.  If you have SAS 9.4, there is also the libname excel option (search for it if needed), which creates a libname directly to an Excel file.  

 

Do remember however that Excel has a lot of "features" which may mess around with your data.  I would never recommend using that application for any purpose, however in some cases we are forced to use it.

Occasional Contributor CG1
Occasional Contributor
Posts: 13

Re: export to excel

Hi,

 

Thanks for replying. I used your first method creating tagsets.excelxp. after my proc sql I execute below statement.

 

ods tagsets.excelxp file="L:\TST\example2.xml";
proc report data=work.results nownd;
columns _all_;
run;
ods tagsets.excelxp close;

 

It creates the excel file in L drive but when I open  it says "Unable to read file".

Super User
Super User
Posts: 7,721

Re: export to excel

Well, there was a typo in my program, but this shouldn't affect the output.  Can you try just using this:

ods tagsets.excelxp file="c:\temp\example.xml";
proc report data=sashelp.cars nowd;
columns _all_;
run;
ods tagsets.excelxp close;

Should create a file which can then be opened in Excel.  Also, check your log to see if the dataset was created, and if there are any other warnings/errors.

Contributor
Posts: 20

Re: export to excel

You may want to check that you have the most updated version of the ExcelXP tagset.

 

http://support.sas.com/rnd/base/ods/odsmarkup/index.html

Ask a Question
Discussion stats
  • 4 replies
  • 415 views
  • 0 likes
  • 3 in conversation