Error when export data to excel, SAS 9.3 x64

Reply
Frequent Contributor
Posts: 130

Error when export data to excel, SAS 9.3 x64

I tried the following two ways to export data to excel or html, but failed.

(I prefer output in excel or CSV for further usage. output in html or other display format is less useful.)

System:

OS: Win7, SP1, Build7601, x64-based PC

SAS: 9.3 TS Level 1M1 x64_7PRO platform

EXCEL: 2013 pro plus, ver15.0.4569.1506

Approach 1

proc export dbms = excel    /*dbms = excelcs also doesn't work*/

     outfile = "C:\outputs\reg.xls"

     data=did_1 replace;

     sheet = "did_1";

run;

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Connection Failed.  See log for details.

Approach 2

If I use ODS CSV or ODS HTML, this code works well:

ODS CSV PATH="C:\outputs" FILE="DiD_1.CSV";

proc reg data=WANT_1 outest = did_1 tableout ;

model y = x z / SPEC WHITE;

run;

ODS CSV CLOSE;

However, if I put the above code into the following macro, it doesn't work. No error message in log window, but all CSV files created by the macro contain no data. I try ODS HTML, the same problem:

%macro DiDreg;

%do i = 1 %to 3;

ODS CSV PATH="C:\outputs" FILE="DiD_1..CSV";  /*if here is: ODS CSV FILE="C:\outputs\DiD_1..CSV"; the output csv or html files still contain no data*/

proc reg data=WANT_1 outest = did_1 tableout ;

model y = x z / SPEC WHITE;

run;

ODS CSV CLOSE;

%end;

%mend;

%DiDreg;

Super User
Posts: 17,837

Re: Error when export data to excel, SAS 9.3 x64

It seems weird to use CSV to store output from proc reg, but lets go with it.

Try Tagsets.ExcelXP instead.

If it doesn't work, it helps if you post the log as well Smiley Happy

%macro DiDreg;

%do i = 1 %to 3;

ODS Tagsets.excelxp File="C:\outputs\DiD_&i..xml" style=journal; 

proc reg data=WANT_1 outest = did_1 tableout ;

model y = x z / SPEC WHITE;

run;

ODS tagsets.excelxp CLOSE;

%end;

%mend;

%DiDreg;

Frequent Contributor
Posts: 130

Re: Error when export data to excel, SAS 9.3 x64

Thank you Reeza. I use CSV since I need data of the output, not just the table in display format.

After running the code, no error, but the xml files created are not readable by excel, or other programs.

log is:

NOTE: Writing TAGSETS.EXCELXP Body file: C:\outputs\DiD_3.xml

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011). Add options(doc='help') to the ods statement for more information.

%macro DiDreg;

%do i = 1 %to 3;

ODS Tagsets.excelxp File="C:\outputs\DiD_&i..xml" style=journal;

proc reg data=WANT_&i outest = did_&i tableout ;

model y = x z / SPEC WHITE;

run;

ODS tagsets.excelxp CLOSE;

%end;

%mend;

%DiDreg;

Super User
Posts: 17,837

Re: Error when export data to excel, SAS 9.3 x64

They files should open in Excel, if they aren't, I'd suggest opening a track with tech support. You might have to open Excel and then open the files from in it.

In the mean time, do you actually need the full output from the regression output or only a specific portion? If its a specific portion, you can capture that into a table and then write that directly to a CSV file.

Frequent Contributor
Posts: 130

Re: Error when export data to excel, SAS 9.3 x64

I had tried to open from Excel-Files-Open, didn't work.

Since I have dozens of results, copy paste looks too much work. Thank you for your advice anyway.

Super User
Posts: 17,837

Re: Error when export data to excel, SAS 9.3 x64

I wasn't suggesting copy/paste. You can capture almost all of the output generated by SAS using ODS Table Names, format them, and then do a straight Proc Export.

EDIT:

How do I get my SAS results into a data set? | Statistics and other stuff from a geek

Valued Guide
Posts: 3,208

Re: Error when export data to excel, SAS 9.3 x64

You cannot use a 64 bit sas system to connect to a 32 bit office system.

A possible approach is using the pcfiles server 32 bit version. This one can connect to 32 bit office and is an interface coming from every  type of server.

---->-- ja karman --<-----
Frequent Contributor
Posts: 130

Re: Error when export data to excel, SAS 9.3 x64

thanks!

Ask a Question
Discussion stats
  • 7 replies
  • 666 views
  • 3 likes
  • 3 in conversation