CODEWITHSUNDEEP
sasexport-to-excel
StatMan
StatMan

Reputation: 636

How to export xlsx files in SAS

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?

Upvotes: 2

Views: 24914

Answers (3)

Kuldeep Kumar
Kuldeep Kumar

Reputation: 1

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;

Upvotes: 0

Joe
Joe

Reputation: 63424

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.

Upvotes: 0

Grinvydas Kareiva
Grinvydas Kareiva

Reputation: 315

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

Upvotes: 1

Related Questions