topic Re: export to excel in Microsoft Integration with SAS

export to excel

CG1 — Mon, 16 Nov 2015 10:28:34 GMT

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.

Re: export to excel

RW9 — Mon, 16 Nov 2015 10:58:46 GMT

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.

Re: export to excel

CG1 — Mon, 16 Nov 2015 12:07:46 GMT

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

Re: export to excel

RW9 — Mon, 16 Nov 2015 13:08:56 GMT

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.

Re: export to excel

slangan — Mon, 16 Nov 2015 14:00:03 GMT

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