topic Re: PROC EXPORT DBMS=EXCEL in SAS Procedures

PROC EXPORT DBMS=EXCEL

FPAN — Mon, 19 Dec 2016 09:37:41 GMT

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

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

Re: PROC EXPORT DBMS=EXCEL

Kurt_Bremser — Wed, 26 Jul 2017 17:20:14 GMT

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;

Re: PROC EXPORT DBMS=EXCEL

Ksharp — Mon, 19 Dec 2016 10:05:15 GMT

Try another engine.

dbms=xls

Re: PROC EXPORT DBMS=EXCEL

RW9 — Mon, 19 Dec 2016 10:19:01 GMT

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.

Re: PROC EXPORT DBMS=EXCEL

FPAN — Mon, 19 Dec 2016 11:08:20 GMT

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=;

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

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

Re: PROC EXPORT DBMS=EXCEL

FPAN — Mon, 19 Dec 2016 11:11:23 GMT

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

Re: PROC EXPORT DBMS=EXCEL

FPAN — Mon, 19 Dec 2016 11:13:00 GMT

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

Re: PROC EXPORT DBMS=EXCEL

FPAN — Mon, 19 Dec 2016 11:13:36 GMT

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

Re: PROC EXPORT DBMS=EXCEL

LinusH — Mon, 19 Dec 2016 11:26:06 GMT

It seems that this is just a partial output.

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

Re: PROC EXPORT DBMS=EXCEL

FPAN — Mon, 19 Dec 2016 11:31:12 GMT

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

Re: PROC EXPORT DBMS=EXCEL

Kurt_Bremser — Mon, 19 Dec 2016 12:09:50 GMT

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)

Re: PROC EXPORT DBMS=EXCEL

LinusH — Mon, 19 Dec 2016 21:01:17 GMT

"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.

Re: PROC EXPORT DBMS=EXCEL

newbornalive — Thu, 18 Jan 2018 21:16:44 GMT

The Access Database Engine-64Bit 2010-14.0.7015.1000 package might be missing.

Re: PROC EXPORT DBMS=EXCEL

RW9 — Fri, 19 Jan 2018 09:16:06 GMT

Two minor points. First this post is 2 years old. Second the title is dbms=excel, nothing to do with Access.

Re: PROC EXPORT DBMS=EXCEL

srinath3111 — Fri, 19 Jan 2018 09:28:03 GMT

Hi,

try xlsx instead of xls.

proc export outfile= "path/filename.xlsx"

data=dataset name

dbms=excel

replace;

run;

Thanks

Re: PROC EXPORT DBMS=EXCEL

RW9 — Fri, 19 Jan 2018 09:45:10 GMT

Please stop posting on a 2 year old closed thread!

Re: PROC EXPORT DBMS=EXCEL

newbornalive — Fri, 19 Jan 2018 17:43:35 GMT

True but, it is what happened to my code and the IT team debugged it this way.

Re: PROC EXPORT DBMS=EXCEL

sjh485 — Thu, 25 Jan 2018 05:41:00 GMT

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

Re: PROC EXPORT DBMS=EXCEL

quickbluefish — Fri, 27 Jul 2018 17:31:10 GMT

Hi - actually "Access" in this case has nothing to do with MS Access.

Re: PROC EXPORT DBMS=EXCEL

USHAKS — Tue, 25 Sep 2018 14:56:08 GMT

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.