BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10
%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 ;
1 ACCEPTED SOLUTION

Accepted Solutions
Nasser_DRMCP
Lapis Lazuli | Level 10

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

24 REPLIES 24
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

 

%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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

 

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
Super User

@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
Lapis Lazuli | Level 10

17 000 observ in the dataset

Nasser_DRMCP
Lapis Lazuli | Level 10

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
Super User

@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
Lapis Lazuli | Level 10

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
Super User

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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