topic Re: macro to export dataset into excel file : a blank at end in SAS Programming

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

Nasser_DRMCP — Mon, 09 Apr 2018 10:22:15 GMT

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

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

RW9 — Mon, 09 Apr 2018 10:47:52 GMT

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.

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

Kurt_Bremser — Mon, 09 Apr 2018 11:01:01 GMT

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 ;

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

Nasser_DRMCP — Mon, 09 Apr 2018 12:20:56 GMT

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 ;

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

Nasser_DRMCP — Mon, 09 Apr 2018 12:31:28 GMT

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

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

RW9 — Mon, 09 Apr 2018 12:48:35 GMT

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.

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

Nasser_DRMCP — Mon, 09 Apr 2018 15:04:03 GMT

%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

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

Reeza — Mon, 09 Apr 2018 15:11:48 GMT

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

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

RW9 — Mon, 09 Apr 2018 15:26:12 GMT

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.

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

Nasser_DRMCP — Mon, 09 Apr 2018 15:46:08 GMT

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

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

Kurt_Bremser — Mon, 09 Apr 2018 15:51:05 GMT

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

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

Reeza — Mon, 09 Apr 2018 15:52:57 GMT

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

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

Nasser_DRMCP — Mon, 09 Apr 2018 16:02:54 GMT

17 000 observ in the dataset

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

Nasser_DRMCP — Mon, 09 Apr 2018 16:06:17 GMT

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 ;

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

Reeza — Mon, 09 Apr 2018 16:09:25 GMT

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

What about a straight PROC EXPORT then?

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

Nasser_DRMCP — Mon, 09 Apr 2018 16:23:14 GMT

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.

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

Reeza — Mon, 09 Apr 2018 16:26:19 GMT

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;

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

Nasser_DRMCP — Mon, 09 Apr 2018 16:30:20 GMT

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

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

Reeza — Mon, 09 Apr 2018 16:32:43 GMT

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;

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

Nasser_DRMCP — Mon, 09 Apr 2018 16:36:26 GMT

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