topic Re: PROC SQL Export to Excel Actual Result did not happen in SAS Enterprise Guide

PROC SQL Export to Excel Actual Result did not happen

sdang — Tue, 18 Oct 2016 20:23:42 GMT

HI,

I have SAS EG version 6.1(64-bit). I want to export a dataset into an already created xls file. The codes went through without any errors but nothing in the file when i opened it. The file, "Test" is completey blank. Below are my codes. Appreciate the help.

proc sort data=WORK.QUERY_FOR_QUERY_TABLE_PRELOADS out=class;

by 'Type of Mailer'n;

where 'Type of Mailer'n in ('Active - Low Freq');

run ;quit;

proc export

data =work.class

outfile ='C:\Users\User\Documents\My SAS Files\Test.xls'

dbms=xls replace;

sheet='Test';

quit;

Re: PROC SQL Export to Excel Actual Result did not happen

Astounding — Tue, 18 Oct 2016 20:50:05 GMT

What did the log say about the number of observations in CLASS?

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Tue, 18 Oct 2016 21:35:21 GMT

Thank you for replying. Below are what the log says:

NOTE: The export data set has 23100 observations and 67 variables.

NOTE: "C:\Users\sdang\Documents\My SAS Files\Test.xls" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 2.12 seconds

cpu time 0.35 seconds

Re: PROC SQL Export to Excel Actual Result did not happen

SASKiwi — Tue, 18 Oct 2016 23:42:02 GMT

What happens if you remove the SHEET statement?

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Tue, 18 Oct 2016 23:50:27 GMT

Thank you for replying. I deleted the Sheet statment and the file is still empty. No records were added on. Again my codes are below.

proc sort data=WORK.QUERY_FOR_QUERY_TABLE_PRELOADS out=Query_For_class;

by 'Type of Mailer'n;

where 'Type of Mailer'n in ('Active - Low Freq');

run ;

proc export

data =work.query_for_class

outfile ='C:\Users\sdang\Documents\My SAS Files\Test.xls'

dbms=xls replace;

quit;

Re: PROC SQL Export to Excel Actual Result did not happen

rogerjdeangelis — Wed, 19 Oct 2016 00:46:29 GMT

In this case you do not need 'proc export'.

libname xel "d:\cls\class.xlsx";
proc sort data=sashelp.class out=xel.class_sort;
by name;
run;quit;

Re: PROC SQL Export to Excel Actual Result did not happen

SASKiwi — Wed, 19 Oct 2016 06:35:27 GMT

Try DBMS = EXCEL. I find that usually works better.

Re: PROC SQL Export to Excel Actual Result did not happen

Patrick — Wed, 19 Oct 2016 10:51:17 GMT

Both code versions below worked for me in my SAS9.4 Windows environment. Code version 1 is pretty much the same than what you've posted.

Are you really sure that there are any observations in your WORK.CLASS table?

/* version 1 */
proc sort data=sashelp.class out=class;
  by name;
run;

quit;

proc export
  data =work.class
  outfile ='C:\temp\Test.xls'
  dbms=xls replace;
  sheet='Test';
quit;

/* version 2 */
libname out xlsx 'C:\temp\Test2.xlsx';
data out.test;
  set work.class;
run;

libname out clear;

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Wed, 19 Oct 2016 16:56:05 GMT

Thank you for replying. I follow your instructions and SAS gives me an error:

libname xel 'C:\Users\sdang\Documents\My SAS Files\LoginTest.xlsx';

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

My codes are :

libname xel 'C:\Users\sdang\Documents\My SAS Files\LoginTest.xlsx';

proc sort data=work.totalgamingdata out=Query_For_Class;

by PlayerID;

where PlayerID eq (459623);

run; quit;

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Wed, 19 Oct 2016 17:12:37 GMT

Thank you for replying. I tried it with DBMS=EXCEL and SAS gives me an error:

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Connection Failed. See log for details.

NOTE: The SAS System stopped processing this step because of errors.

Below are my codes:

proc sort data=work.totalgamingdata out=Query_For_Class;

by PlayerID;

where PlayerID eq (200148);

run;

proc export data=work.query_for_class

outfile='C:\Users\sdang\Documents\My SAS Files\LoginTest.xls'

dbms=excel replace ;

sheet='Test';

quit;

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Wed, 19 Oct 2016 17:28:34 GMT

Thank you for replying. I tried version 1 and the export was successful but still no data in the excel file. This is what the log says about the export:

NOTE: The export data set has 2 observations and 12 variables.

NOTE: "C:\Users\sdang\Documents\My SAS Files\LoginTest.xls" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.07 seconds

cpu time 0.00 seconds

And below are my codes:

proc sort data=work.totalgamingdata out=Query_For_Class;

by PlayerID;

where PlayerID eq (2399948);

run;

proc export data=work.query_for_class

outfile='C:\Users\sdang\Documents\My SAS Files\LoginTest.xls'

dbms=xls replace ;

sheet='Test';

quit;

Thank you for your help. If i can figure this out, it would save my department countless hours in manual process.

Re: PROC SQL Export to Excel Actual Result did not happen

SASKiwi — Wed, 19 Oct 2016 18:57:09 GMT

The CLASS errors you are getting are caused by 64-bit SAS trying to export to 32-bit Excel:

http://support.sas.com/kb/52/649.html

A solution to the errors is to use the SAS PC Files Server as mentioned in the SAS note.

An alternative is to download the 64-bit Microsoft Office ACE engine components freely avalable from Microsoft - this is the one we use and it avoids having to run the PC Files Server:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Wed, 19 Oct 2016 20:00:38 GMT

Thank you for replying. I tried your second option and use these codes:

proc export data=work.query_for_class

outfile='C:\Users\sdang\Documents\My SAS Files\LoginTest.xls'

dbms=xls replace;

sheet='Test';

quit;

It does not give me errors message. The codes went through successfully but when i open the file, it is empty.

Re: PROC SQL Export to Excel Actual Result did not happen

SASKiwi — Thu, 20 Oct 2016 03:56:28 GMT

What happens if you use a SAS-supplied dataset: SASHELP.CLASS?

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Thu, 20 Oct 2016 16:17:38 GMT

Thank you for replying. I tried your method by using SASHELP.Class. Same thing, it went through with no errors but nothing in the excel file. It is empty. below are the log:

NOTE: The export data set has 19 observations and 5 variables.

NOTE: "C:\Users\sdang\Documents\My SAS Files\LoginTest.xls" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.12 seconds

cpu time 0.01 seconds

ANd below are my codes:

proc sort data=work.totalgamingdata out=Query_For_Class;

by PlayerID;

where PlayerID eq (200148);

run;

proc export data=sashelp.class

outfile='C:\Users\sdang\Documents\My SAS Files\LoginTest.xls'

dbms=xls replace;

sheet='Test';

quit;

I believe SAS sever is not link to my PC even though i can save a project on my C-drive, import and export(using the task) a file into my C-drive.

Re: PROC SQL Export to Excel Actual Result did not happen

SASKiwi — Thu, 20 Oct 2016 19:25:29 GMT

I suggest you open a track with SAS Tech Support. There is really nothing wrong with your SAS code or your SAS log. Tech Support can troubleshoot your problem in more detail.

Also check the date and time in Windows Explorer of LoginTest.xls to confirm if it got updated or not and try deleting the Excel file first before running your program

Re: PROC SQL Export to Excel Actual Result did not happen

sdang — Thu, 20 Oct 2016 19:28:48 GMT

Thank you for replying. Out BI tech team know what the problem is.