DATA Step, Macro, Functions and more

export to excel

Reply
New Contributor
Posts: 4

export to excel

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

 

 

Super User
Posts: 6,966

Re: export to excel

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: export to excel

How do i find what engine i am using? 

 

Should i be using xl out xlsx = " "

Super User
Posts: 6,966

Re: export to excel


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: export to excel

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

 

PROC Star
Posts: 7,364

Re: export to excel

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

 

New Contributor
Posts: 4

Re: export to excel

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??

PROC Star
Posts: 7,364

Re: export to excel

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

 

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