Help using Base SAS procedures

Export SAS 9.4 (64 bit) into Excel (32 bit)

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Export SAS 9.4 (64 bit) into Excel (32 bit)

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


Accepted Solutions
Solution
‎08-04-2015 04:19 PM
SAS Super FREQ
Posts: 8,866

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

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


All Replies
SAS Super FREQ
Posts: 8,866

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

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;

Super User
Super User
Posts: 7,961

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

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.

New Contributor
Posts: 4

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

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!

Solution
‎08-04-2015 04:19 PM
SAS Super FREQ
Posts: 8,866

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

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

New Contributor
Posts: 4

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

Posted in reply to Cynthia_sas

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!

Trusted Advisor
Posts: 3,214

Re: Export SAS 9.4 (64 bit) into Excel (32 bit)

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 --<-----
🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 2567 views
  • 6 likes
  • 4 in conversation