BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sdang
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
sdang
Quartz | Level 8

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

View solution in original post

16 REPLIES 16
Astounding
PROC Star

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

sdang
Quartz | Level 8

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

 

 

SASKiwi
PROC Star

What happens if you remove the SHEET statement?

sdang
Quartz | Level 8

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;

rogerjdeangelis
Barite | Level 11
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;
sdang
Quartz | Level 8

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;

 

SASKiwi
PROC Star

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

sdang
Quartz | Level 8

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;

 

 

 

Patrick
Opal | Level 21

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;

 

sdang
Quartz | Level 8

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.

 

 

SASKiwi
PROC Star

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

 

 

sdang
Quartz | Level 8

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.

SASKiwi
PROC Star

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

sdang
Quartz | Level 8

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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