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

Hello! Sorry for the rudimentary question, I could really use some guidance though!

I am trying export data from SAS 9.4 (the 64 bit edition) to Microsoft Excel (of which I have the 32 bit version).

I am using the export wizard, and am choosing "Microsoft Excel Workbook on PC Files Server" as the data source.

I input the location I want my workbook to be in (the workbook is .xlsx just fyi), but an error message shows: "ERROR: Failed to connect to the server."

How do I fix this issue?? I know i cannot use "Microsoft Excel Workbook" as the selection since the two softwares have different bits.

Help would be much appreciated! Thanks!

Lexie

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  SASHELP.CLASS is a SAS7BDAT file. Most SAS data files have the SAS7BDAT extension on Windows and Unix. The key to specifying the correct LIBRARY name instead of SASHELP is to know where your data are located. So let me ask you this, in the code I posted, there were 2 file specifications that you had to provide:

proc export data=sashelp.class

            outfile="c:\temp\test_reg.xls"

                   dbms=xls replace label;

   sheet="test2 with xls";

run;


You said in your post that " the program does not read the file location when I use it to replace the sashelp.class file"  -- this implies to me that you are using either the wrong library or dataset name for the DATA= option in the code from my #2 example (shown in pink in the code above). Or, you do not correctly know how to specify a LIBNAME statement. For example, let's say that you have a sas dataset called "ORDERS.SAS7BDAT" on your shared drive (the Q drive) in a folder called Q:\allmydata\accounting\orders.sas7bdat. In order to specify a 2 level name correctly, you first need a LIBNAME statement to point to the Q: drive location:

libname mydata 'q:\allmydata\accounting';

then in your code, you would specify:

proc export data=mydata.orders

            outfile="c:\temp\orders.xls"

                   dbms=xls replace label;

   sheet="test2 with xls";

run;

Note how the library reference name of mydata (which you can think of as a "nickname" or "alias", but which SAS calls a "libref") is the high-level part of the 2 level name mydata.orders in the DATA=option in the code.

On the other hand, if your SAS data is a temporary file and is "living" in the WORK library, then you should be able to refer to it with either a 1 level name DATA=ORDERS or a 2-level name DATA=WORK.ORDERS. It really depends on whether you are referencing a permanent SAS dataset or a temporary one. Without seeing your EXACT code and the EXACT error message, it is nearly impossible to comment on how you should fix the issue you have. You might consider opening a track with Tech Support on this.

cynthia

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

Hi:

  This may be a question for Tech Support. I am not sure that the Export Wizard will work in the scenario you describe. When I have 64 bit SAS and 32 bit Excel, any of these code methods works for me (assuming I do have the PC File Server installed). If you do not have the PC File Server installed then I am not sure that choice in the Export Wizard will work.

Cynthia

%let path=c:\temp;

proc export data=sashelp.class

            outfile="&path\test_x97.xls"

                   dbms=excelcs replace label;

   sheet="test1 with excelcs";

run;

  

proc export data=sashelp.class

            outfile="&path\test_reg.xls"

                   dbms=xls replace label;

   sheet="test2 with xls";

run;

proc export data=sashelp.class

            outfile="&path\test_xlx.xlsx"

                   dbms=xlsx replace label;

   sheet="test3 with xlsx";

run;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

If that doesn't work, and as your using Excel it has a 50-50 likelihood.  Try proc reporting the data to tagsets.excelxp.  As that is a tagset and creates markedup text document it should not matter what bit the Excel version is:

ods tagsets.excelxp file="xyz.xlsx";

proc report data....

run;

ods tagsets.excelxp close;

Alternatively you could export as CSV, that is openable in any version.

Lexie_c
Calcite | Level 5

Thank you Cynthia! The second option worked for me and I was able to export the sashelp.class file into excel!

However, I have ran into a problem since the file I am trying to export is an sas7bdat file, and the program does not read the file location when I use it to replace the sashelp.class file.

Sorry if this is a more rudimentary question, I'm still trying to figure this program out.

Thank you everyone!

Cynthia_sas
SAS Super FREQ

Hi:

  SASHELP.CLASS is a SAS7BDAT file. Most SAS data files have the SAS7BDAT extension on Windows and Unix. The key to specifying the correct LIBRARY name instead of SASHELP is to know where your data are located. So let me ask you this, in the code I posted, there were 2 file specifications that you had to provide:

proc export data=sashelp.class

            outfile="c:\temp\test_reg.xls"

                   dbms=xls replace label;

   sheet="test2 with xls";

run;


You said in your post that " the program does not read the file location when I use it to replace the sashelp.class file"  -- this implies to me that you are using either the wrong library or dataset name for the DATA= option in the code from my #2 example (shown in pink in the code above). Or, you do not correctly know how to specify a LIBNAME statement. For example, let's say that you have a sas dataset called "ORDERS.SAS7BDAT" on your shared drive (the Q drive) in a folder called Q:\allmydata\accounting\orders.sas7bdat. In order to specify a 2 level name correctly, you first need a LIBNAME statement to point to the Q: drive location:

libname mydata 'q:\allmydata\accounting';

then in your code, you would specify:

proc export data=mydata.orders

            outfile="c:\temp\orders.xls"

                   dbms=xls replace label;

   sheet="test2 with xls";

run;

Note how the library reference name of mydata (which you can think of as a "nickname" or "alias", but which SAS calls a "libref") is the high-level part of the 2 level name mydata.orders in the DATA=option in the code.

On the other hand, if your SAS data is a temporary file and is "living" in the WORK library, then you should be able to refer to it with either a 1 level name DATA=ORDERS or a 2-level name DATA=WORK.ORDERS. It really depends on whether you are referencing a permanent SAS dataset or a temporary one. Without seeing your EXACT code and the EXACT error message, it is nearly impossible to comment on how you should fix the issue you have. You might consider opening a track with Tech Support on this.

cynthia

Lexie_c
Calcite | Level 5

Thank you!! I followed your instructions on specifying the dataset name and was able to correctly export the file. As it was a rather large file, the third option you wrote worked very well.

Thanks Cynthia, and sorry for the rather basic question!

jakarman
Barite | Level 11

Try using the XLSX engine (exists with 9.3 proc export) laterst release 9.4 is having a libname xlsx. Bypassing the ACE driver will free you of those limitations.

---->-- ja karman --<-----

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 6403 views
  • 6 likes
  • 4 in conversation