PROC EXPORT DBMS=EXCEL

[FPAN]

Bonjour,

J'utilse SAS 9.2 et je souhaiteri exporter une table SAS sous Excel (idéalement en .xlsx)

Le code est le suivant :

proc export data=sashelp.class dbms=excel

outfile="C:\temp\Class.xls";

run;

Celà ne fonctionne pas dès que j'utilise l'option DBMS=

    15 proc export data=sashelp.class dbms=excel

    16

    17 outfile="C:\temp\Class.xls";

    ERROR: DBMS type EXCEL not valid for export.

    NOTE: Le Système SAS a interrompu le traitement de cette étape en raison d'erreurs.

Par contre l'export en CSV avec le code suivant fonctionne :

proc export data=sashelp.class

outfile="C:\temp\Class.csv";

run;

Pourriez-vous m'aider svp ?

Cdt

[Kurt_Bremser · Accepted solution]

Do you have SAS/ACCESS Interface to PC Files licensed?

Run

proc setinit;
run;

to see which SAS modules are licensed at your site.

Editor's note:

If you don't have SAS/ACCESS to PC Files, then DBMS=EXCEL or XLS or XSLX can't work.  However, you can create Excel output by using ODS EXCEL.

ods excel file="c:\temp\class.xlsx"

proc print data=sashelp.class;
run;

ods excel close;

[FPAN]

Indeed I have MS Access installed on my PC, here the log for this code

proc setinit;
run;

=>>>

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='Programme1';

4 %LET _CLIENTPROJECTPATH='';

5 %LET _CLIENTPROJECTNAME='';

6 %LET _SASPROGRAMFILE=;

7

8 ODS _ALL_ CLOSE;

9 OPTIONS DEV=ACTIVEX;

NOTE: Procedures may not support all options or statements for all devices. For details, see the

documentation for each procedure.

10 GOPTIONS XPIXELS=0 YPIXELS=0;

11 FILENAME EGSR TEMP;

12 ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis

12 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/EnterpriseGuide/4.3/Styles/Analysis.css")

12 ! NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

SYMBOLGEN : Macro variable SASWORKLOCATION traitée dans

"C:\Users\7312912T\AppData\Local\Temp\SEG4796\SAS Temporary Files\_TD3724\Prc2/"

NOTE: Ecriture du corps du fichier TAGSETS.SASREPORT12(EGSR) : EGSR

13

14 GOPTIONS ACCESSIBLE;

　

15 proc setinit;

16 run;

NOTE: Procédure SETINIT a utilisé (Durée totale du traitement) :

temps réel 0.01 secondes

temps UC 0.00 secondes

[LinusH]

It seems that this is just a partial output.

A SAS product listing with end of licence date is expected to follow.

[FPAN]

Here the end :
Données de validation du site originales
Expiration : 31MAR2017.
Délai de grâce : 62 jours (fin le 01JUN2017).
Délai d'avertissement : 33 jours (fin le 04JUL2017).
Anniversaire du système : 30MAR2016.
Système d'exploitation : W32_WKS .
Dates d'expiration du produit :
---Produit Base 31MAR2017
---SAS Workspace Server for Local Access 31MAR2017

[Kurt_Bremser]

If these are really all your products, then ACCESS to PC Files is not licensed (don't confuse this with SAS Workspace Server for Local Access)

[USHAKS]

_

I get this error. Please help me to resolve it__
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, ANCHOR, AUTHOR, BOX_SIZING, CATEGORY, CLOSE, COMMENTS, CSSSTYLE,
DOM, DPI, FILE, GFOOTNOTE, GTITLE, IMAGE_DPI, KEYWORDS, NOGFOOTNOTE, NOGTITLE, OPTIONS, SASDATE, STATUS, STYLE, TEXT,
TITLE, WORK.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

[Kurt_Bremser]

Please open your own thread for your question, and include the whole log of the step in a window opened with the {i} button.

[Ksharp]

Try another engine.

dbms=xls 

[FPAN]

I tried with DBMS=XLS but it's not working
15 proc export data=sashelp.class dbms=xls
16
17 outfile="C:\temp\Class.xls";
ERROR: DBMS type XLS not valid for export.

A bit weird, the option DBMS= seems not to be tolerated

[sjh485]

Thank you.

The following worked for SAS 9.4, 64 bit on windows 7.

proc export data = d1 dbms = xls
  outfile = "c:\d1.xls";
run;

so did

ods excel file="c:\d1.xlsx";
  proc print data=d1;
run;
ods excel close;

🙂

Stephen

[RW9]

Use:

ods tagsets.excelxp file=...;

proc report data...;

This creates XML output which Excel can read directly.  But you do get a good spread of coloring, formatting etc. which with Export you don't get.

It depends on your requirements, if this is to send data to someone, then don't use Excel.  Use CSV (which isn't Excel!) or XML or other data transfer format.  If its for a report, then use proc report to generate a report output.

[FPAN]

Thank you for the tip, I will try and keep you posted shortly

[FPAN]

Thank you all for your prompt reply, this is very helpful ! 🙂

[LinusH]

"SAS Workspace Server for Local Access"
It sounds like that you have Enterprise Guide...?
If so, there's a built in functionality in EG ro export Excel.