Desktop productivity for business analysts and programmers

PROC SQL Export to Excel Actual Result did not happen

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

PROC SQL Export to Excel Actual Result did not happen

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;


Accepted Solutions
Solution
‎10-20-2016 03:29 PM
Frequent Contributor
Posts: 104

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

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

View solution in original post


All Replies
Super User
Posts: 5,359

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

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

Frequent Contributor
Posts: 104

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

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

 

 

Super User
Posts: 3,233

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

What happens if you remove the SHEET statement?

Frequent Contributor
Posts: 104

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

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;

Valued Guide
Posts: 505

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

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;
Frequent Contributor
Posts: 104

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

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;

 

Super User
Posts: 3,233

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

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

Frequent Contributor
Posts: 104

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

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;

 

 

 

Respected Advisor
Posts: 4,131

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

[ Edited ]

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;

 

Frequent Contributor
Posts: 104

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

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.

 

 

Super User
Posts: 3,233

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

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

 

 

Frequent Contributor
Posts: 104

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

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.

Super User
Posts: 3,233

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

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

Frequent Contributor
Posts: 104

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

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 465 views
  • 1 like
  • 5 in conversation