BookmarkSubscribeRSS Feed
CG1
Calcite | Level 5 CG1
Calcite | Level 5

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.

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

CG1
Calcite | Level 5 CG1
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

slangan
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 4 replies
  • 1150 views
  • 0 likes
  • 3 in conversation