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

[Nasser_DRMCP]

%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)
		,
	QUOTE(LEFT(TRIM(COALESCE(label,name)))),
		COUNT(*),
		MAX(nobs)+1
	INTO : variables SEPARATED BY " '09'x ",
		: en_tete SEPARATED BY " '09'x ",
		: nbVar ,
		: nbObs
	FROM work.dictionnaire
		ORDER BY varnum
	;
Quit;

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

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

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

RUN ;


%mend export_sas_v2 ;

[RW9]

Why?

What do you hope to achieve that:
proc export

tagsets.excelxp / tagsets.excel

ods csv

html output

Does not cover?  Your just creating work for yourself as at a guess your actually creating a tab delimited file.

[Nasser_DRMCP]

thanks for your quick response

by doing that I get the attended result

ODS TAGSETS.EXCELXP FILE = "&xlsfile." style=normal ;

PROC PRINT DATA = &tdb_mens. ;

RUN ;

ODS HTML CLOSE ;

[Nasser_DRMCP]

%Let xlsfile = %str(%')&repwork.&tdb_mens..xml%str(%') ;

[RW9]

Sorry, I am not following what it is your trying to say? What I can tell you is this:

%Let xlsfile = %str(%')&repwork.&tdb_mens..xml%str(%') ;

Will result in the following code, which I don't expect is what you wanted:

ODS TAGSETS.EXCELXP FILE = "'......._mens..xml'" style=normal ;

I.e. with single quotes around it.  It is never a good idea to put quotes in a macro variable,

%Let xlsfile = &repwork.&tdb_mens..xml;

I would avoid coding in mixed or all upper case, it makes code harder to read.  And use code windows for posting code - its the {i} above post area.

[Nasser_DRMCP]

%Let xlsfile = &repwork.&tdb_mens..xml ;

ODS TAGSETS.EXCELXP FILE = "&xlsfile."  style=normal  ;
  PROC PRINT DATA = &tdb_mens. ;
  RUN ;
ODS HTML CLOSE ;

Thanks RW for your help.

I am trying to export a dataset into a flat file wich can be directly opened with excel.  by using ODS TAGSET. the proc "print" seems correct but it takes many elapsed time. SO I do not have the solution yet

[RW9]

It is not suprising it runs forever, you have opened ods tagsets.excelxp and never closed it!

%Let xlsfile = &repwork.&tdb_mens..xml ;

ODS TAGSETS.EXCELXP FILE = "&xlsfile."  style=normal  ;
  PROC PRINT DATA = &tdb_mens. ;
  RUN ;
ODS tagsets.excelxp CLOSE ;

As @Reeza states you can also use Excel to create files if you have a recent version of SAS, as that creates actual Excel files.  If you want plain text file, then ods csv.   

[Nasser_DRMCP]

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

[Kurt_Bremser]

---

@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

---

Tagsets.excelxp writes uncompressed xml, so the resulting file can become very large. How many observations do you have in your dataset?

If you just need to export values to Excel, a csv file is a much better means.

[Nasser_DRMCP]

17 000 observ in the dataset

[Nasser_DRMCP]

Yes Kurt. I just need to export values to Excel.

even if try with csv extension, it takes also a long time

%Let xlsfile = &repwork.&tdb_mens..csv ;

[Kurt_Bremser]

---

@Nasser_DRMCP wrote:

Yes Kurt. I just need to export values to Excel.

even if try with csv extension, it takes also a long time

%Let xlsfile = &repwork.&tdb_mens..csv ;

---

Just changing the extension of a filename will do nothing. Zero. Nada. Zilch.

You have to replace the whole ODS process with a data step that writes the csv in one step.

You can start by using proc export with dbms=csv, and then copy the data step from the log to adapt it, if needed.

[Nasser_DRMCP]

Hello Kurt

thanks.

by doing that, I managed to get a file that I can open into excel.

but I encounter some problems with format.

a number

a text "non classé" is transfered "non classé"

numbers like -3014.3 is correctly transfered but I would like the separator comma (instead of dot)

many thanks to everybody for your precious help

Proc export data=&tdb_mens.
	DBMS=CSV
	outfile="&repwork.&tdb_mens..csv" 
	replace ;
DELIMITER=';'
;
RUn ;

[Kurt_Bremser]

Quote from my previous post:

"and then copy the data step from the log to adapt it, if needed."

In the data step that proc export created you can use formats to your desire.

And you can use an encoding= option in the data statement so that your special characters are reciognized.

[Nasser_DRMCP · Accepted solution]

Hello,

 the best solution is proc export. even if the accent are not correctly tranfered.. because "ods and proc print" It lasts too long.

and I have too specify the format numx10.5 in a previous data step to get the ',' as separator (instaed of '.')

Nasser