Help using Base SAS procedures

PROC EXPORT DBMS=EXCEL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

PROC EXPORT DBMS=EXCEL

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


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 6,926

Re: PROC EXPORT DBMS=EXCEL

[ Edited ]

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 6,926

Re: PROC EXPORT DBMS=EXCEL

[ Edited ]

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: PROC EXPORT DBMS=EXCEL

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

 

Super User
Posts: 5,254

Re: PROC EXPORT DBMS=EXCEL

It seems that this is just a partial output.

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

Data never sleeps
Occasional Contributor
Posts: 6

Re: PROC EXPORT DBMS=EXCEL

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
Super User
Posts: 6,926

Re: PROC EXPORT DBMS=EXCEL

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,656

Re: PROC EXPORT DBMS=EXCEL

Try another engine.

dbms=xls 


Occasional Contributor
Posts: 6

Re: PROC EXPORT DBMS=EXCEL

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
Super User
Super User
Posts: 7,392

Re: PROC EXPORT DBMS=EXCEL

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.

Occasional Contributor
Posts: 6

Re: PROC EXPORT DBMS=EXCEL

Thank you for the tip, I will try and keep you posted shortly
Occasional Contributor
Posts: 6

Re: PROC EXPORT DBMS=EXCEL

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

 

Super User
Posts: 5,254

Re: PROC EXPORT DBMS=EXCEL

"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.
Data never sleeps
☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 3569 views
  • 0 likes
  • 5 in conversation