topic Re: export to excel in SAS Programming

export to excel

PazM — Mon, 12 Jun 2017 10:48:24 GMT

Hi,

I seem to have a problem when i output to an Excel file from SAS 9.4. The export works fine and pastes the data into the tab i chose, however some of the data in other tabs move around to other cells. E.G Headings in column A tab 2 are arranged differently on a sheet which has no output assigned to it.

Not sure why this keeps happening but its abit frustrating having rewrite the row headings in again each time my export is run.

SAS 9.4

Excel 2010 (32 bit)

libname output pcfiles path = "......";

proc sql;
drop table output.Data;
drop table output.'Data$'n;
quit;
proc sql;
create table output.Data as
select *
from Final_Data
;quit;

libname output clear;

I have also tried a normal proc export statement and the same issue is occuring.

Thanks in advance

Re: export to excel

Kurt_Bremser — Mon, 12 Jun 2017 11:16:48 GMT

What engine are you really using? "pcfiles" is not a valid engine name, IIRC.

Re: export to excel

PazM — Mon, 12 Jun 2017 12:22:31 GMT

How do i find what engine i am using?

Should i be using xl out xlsx = " "

Re: export to excel

Kurt_Bremser — Mon, 12 Jun 2017 12:44:22 GMT

@PazM wrote:

How do i find what engine i am using?

Should i be using xl out xlsx = " "

Post the REAL version of

libname output pcfiles path = "......";

with the engine you used in the export.

Re: export to excel

PazM — Fri, 16 Jun 2017 13:25:30 GMT

libname output pcfiles path = "C:\Users\234783\Analysis v4.xlsx";

Re: export to excel

art297 — Fri, 16 Jun 2017 14:14:50 GMT

Ran into a similar problem a couple of months ago. When you open up one of the workbooks look at the upper left hand corner of the Excel screen. Does the string "[Group]" appear to the right of the Workbook name?

Art, CEO, AnalystFinder.com

Re: export to excel

PazM — Fri, 16 Jun 2017 14:24:54 GMT

No i can't see the "[Group]" string next to the workbook name. I'm assuming your thinking that multiple tabs are selected before the export??

Re: export to excel

art297 — Fri, 16 Jun 2017 14:37:27 GMT

Yes, that is precisely what I was thinking. Look again immediately after doing such an export where you see the odd things you described in your original question. Since ungrouping occurs as soon as someone selects a different sheet, you would only notice it immediately after creating the workbook.

Art, CEO, AnalystFinder.com