macro to export dataset into excel file : a blank at end

[Reeza]

Yes, it's slower, especially compared to a PROC EXPORT. Is there a reason you're not using a regular PROC EXPORT instead? I"m not seeing anything that precludes the usage, so as long as you have the SAS Access to PC FILES license it's likely the fastest method.

---

@Nasser_DRMCP wrote:

ODS TAGSETS.EXCELXP FILE = "&xlsfile."  style=normal  ;
  PROC PRINT DATA = &tdb_mens. ;
  format REF_CTR_EXI $15. DAT_DBL_ANCIEN date10. ;
  RUN ;
ODS TAGSETS.EXCELXP CLOSE ;

I am not shure to use a really recent version 7.1 (7.100.0.1966) (64-bit)

by doing that it takes also a long time

---

[Reeza]

---

@Nasser_DRMCP wrote:

thanks for your quick response

by doing that I get the attended result

ODS TAGSETS.EXCELXP FILE = "&xlsfile." style=normal ; *<- THis is ODS TAGSETS;

PROC PRINT DATA = &tdb_mens. ;

RUN ;

ODS HTML CLOSE ; *This should also be ODS TAGSETS.EXCELXP;

---

If you want an XLSX file, use ODS EXCEL (SAS 9.4M3+), otherwise this generates an XML file. 

[Kurt_Bremser]

You are of course aware that you are lying by naming your file .xls. It is in fact a tab-separated text file and should be named .txt.

Newer versions of Excel will always complain when you open such a file with a wrong extension.

And why so complicated?

Let SAS handle most of the tab-separation:

%macro export_sas_v2(folder_out=,fichier=,version=);

PROC CONTENTS DATA = &fichier.
OUT = work.dictionnaire
NOPRINT ;
RUN ;

PROC SQL noprint ;
	SELECT COMPBL(name!!
		CASE
			WHEN (type=1 AND
			format IS MISSING)
			THEN " NUMX15.2 "
		WHEN (type=1) THEN " "!!
		COMPRESS(cats(format,formatL,".",formatD," "))
		ELSE ""
		END)
		,
	LEFT(TRIM(COALESCE(label,name))),
	COUNT(*),
	MAX(nobs)+1
	INTO : variables SEPARATED BY " ",
		: en_tete SEPARATED BY '09'x,
		: nbVar ,
		: nbObs
	FROM work.dictionnaire
		ORDER BY varnum
	;
Quit;

FILENAME xlData "&folder_out.&fichier.&version..txt";

DATA _NULL_ ;
FILE xlData DROPOVER lrecl=50000 dlm='09'x encoding= 'WE8MSWIN1252' ;
SET &fichier. ;

IF _N_ = 1 THEN PUT "&en_tete" ;
PUT &variables ;

RUN ;


%mend export_sas_v2 ;

[Reeza]

proc export data=sashelp.cars outfile="temp.xlsx" dbms=xlsx replace ; run;

What about a straight PROC EXPORT then?

[Nasser_DRMCP]

the required module is not installed. by trying the proc export , the log says this:

The SAS/ACCESS Interface to PC Files is not installed. Please install this module in order to IMPORT/EXPORT to these file types.

[Reeza]

Ah...so in this case you're company was use DDE to get around not purchasing a license - I'm not sure why, the module saves enough time to make it worth it. 

Anyways, then the fastest method is @Kurt_Bremser solution, export (using proc export) to a CSV file instead. 

proc export data=sashelp.cars outfile='test.csv' dbms=csv replace; run;

Unfortunately using ODS is slower, but you don't really have a workaround if you don't have the license. 

If you really want an XLSX file, then ODS EXCEL is the next best option:

ods excel file='demo.xlsx' style=meadow;

proc print data=sashelp.cars noobs label;
run;

ods excel close;

[Nasser_DRMCP]

Thanks reeza

I try your suggestion but...

ERROR: Unable to load module 'SpreadsheetML' from template store!

ERROR: No body file. EXCEL output will not be created

[Reeza]

Post your exact code and log from the code with errors. I'm assuming you updated the path to be something that would work for you?

You may not have ODS EXCEL, run the following to see your SAS version (you've been providing your EG version which can vary). 

proc product_status;run;

[Nasser_DRMCP]

For Base SAS Software ...

Custom version information: 9.3_M2

Image version information: 9.03.01M2P080112

For SAS/STAT ...

Custom version information: 12.1

Image version information: 9.03.01M0P081512

For SAS/GRAPH ...

Custom version information: 9.3_M2

For SAS Integration Technologies ...

Custom version information: 9.3_M2

For SAS/ACCESS Interface to Oracle ...

Custom version information: 9.3_M1

For SAS/ACCESS Interface to ODBC ...

Custom version information: 9.3_M2

For SAS/ACCESS Interface to Netezza ...

Custom version information: 9.3

Image version information: 9.03.01M0P051111

[Reeza]

Ok, 9.3 does not support ODS EXCEL and does not support XLSX engine fully either. So you're stuck with either exporting to a CSV or using ODS TAGSETS as illustrated earlier. 

Note that this can all be solved by upgrading to 9.4 TS1M5 and/or purchasing the SAS Access to PC Files. A brief note to your manager about how much time was spent trying to work around these things should be enough to be a push for an upgrade.

9.3M2 was released in 2012 so it's about 6 years old. Not too bad, but still out of date as software goes.