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.
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.
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".
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.
You may want to check that you have the most updated version of the ExcelXP tagset.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!