How to export xlsx files in SAS

1
votes

The following two exports, will give an error on my machine (in both SAS base and SAS Enterprise Guide):

proc  export data=  sashelp.shoes
            outfile= " D:\SAS\myfile.xlsx"
            dbms=xlsx replace;
       sheet="Shoes";
run;

proc  export data=  sashelp.class
            outfile= " D:\SAS\myfile.xlsx"
            dbms=excelcs replace;
       sheet="Class";
run;

as these exports produce the following errors respectively:

ERROR: DBMS type XLSX not valid for export.
ERROR: DBMS type EXCELCS not valid for export.

By browsing the internet I think that the problem is caused by exporting the file from a 64-bit SAS version to a 32-bit Excel version.

I downloaded and installed pcfilesrv__931401__win__en__web__1.zip from the SAS Support website and hoped it would solve the problem, however the errors still occur.

Anybody with another idea?

Specs:

Windows 64-bit Operating System

SAS Enterprice Guide 5.1 (64-bit)

SAS Base 9.3 (64-bit)

Excel 2013 (32-bit)

EDIT: @Grinvydas Kareiva mentioned in his answer that I needed the "SAS/Access Interface to PC Files". This installation wizard after running setup.exe in the zip file I downloaded from the SAS Support website (see above). Install Wizard

However, when I run proc setinit, it doesn't show up anywhere (changed site number and name manually):

Site name:    'xxxxxxx'.
Site number:  xxxxxx.
Expiration:   01SEP2017.
Grace Period:  62 days (ending 02NOV2017).
Warning Period: 31 days (ending 03DEC2017).
System birthday:   01NOV2016.
Operating System:   WX64_WKS.
Product expiration dates:
---Base SAS Software                                                                                    01SEP2017  
---SAS/STAT                                                                                             01SEP2017  
---SAS/GRAPH                                                                                            01SEP2017  
---SAS Enterprise Guide                                                                                 01SEP2017  
---SAS Workspace Server for Local Access                                                                01SEP2017

Am I doing something wrong?

3
sasexport-to-excel
Would DBMS=XLS work? (I don't think it requires any add-ons, but not sure.) – pinegulf
Unfortunately, it does not. [csv on the other hand works, however I need it to be a xlsx due to automatic process after SAS which expect xlsx]. The more I read about it, it looks to me like it is a bug in SAS 9.3 64-bit, which can't export anything to 32-bit Office products. So my guess is that all exports with an 'Office product extension' will fail. – StatMan

3 Answers

1
votes

Firstly, you need to have licensed "SAS/ACCESS Interface to PC Files". You can check that in the log after submitting

proc setinit;
run;

One of these should work if you have that interface

dbms=excel or dbms=xlsx
0
votes

In SAS 9.4 TS1M1 and later, you can use ods excel to export actual xlsx files without the license for ACCESS to PC FILES. In SAS 9.3, this functionality isn't available.

0
votes
use the below code to export excel or csv file 
/*import the .xlsx or csv file */
FILENAME REFFILE '/<path to file>/Statistic_Details.xlsx';

PROC IMPORT DATAFILE=REFFILE
    DBMS=XLSX
    OUT=libref.Statistic_Details;
    GETNAMES=YES;
RUN;

PROC CONTENTS DATA=libref.Statistic_Details; RUN;