DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

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

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

Accepted Solutions
Solution
‎04-16-2018 03:28 AM
Frequent Contributor
Posts: 117

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

Posted in reply to Nasser_alfea

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

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

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 117

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

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 ;

 

Frequent Contributor
Posts: 117

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

Posted in reply to Nasser_alfea

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

Super User
Super User
Posts: 9,840

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

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 117

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

 

%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

 

Super User
Super User
Posts: 9,840

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

Posted in reply to Nasser_alfea

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.   

Frequent Contributor
Posts: 117

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

 

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

Super User
Posts: 10,570

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

Posted in reply to Nasser_alfea

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 117

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

Posted in reply to KurtBremser

17 000 observ in the dataset

Frequent Contributor
Posts: 117

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

Posted in reply to KurtBremser

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 ;

Super User
Posts: 10,570

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

[ Edited ]
Posted in reply to Nasser_alfea

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 117

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

Posted in reply to KurtBremser

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 ;
Super User
Posts: 10,570

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

Posted in reply to Nasser_alfea

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎04-16-2018 03:28 AM
Frequent Contributor
Posts: 117

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

Posted in reply to Nasser_alfea

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

☑ This topic is solved.

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

Discussion stats
  • 24 replies
  • 174 views
  • 2 likes
  • 4 in conversation