DATA Step, Macro, Functions and more

TAGSETS.EXCELXP (format problem in proc report)

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

TAGSETS.EXCELXP (format problem in proc report)

Hi,

i want create a xml file with this code:

ODS TAGSETS.EXCELXP file="C:\Users\ui43925\Desktop\PROVA\&FILE." 
				style=printer
				OPTIONS ( Center_Horizontal = 'yes'
				Embedded_Titles = 'yes'
				Embedded_Footnotes = 'no' 
				Sheet_Name = 'Elenco terminali'
				/* Absolute_Column_Width = 'NONE' */
				Center_Horizontal = 'yes'
				Autofit_Height = 'yes'
				/*Frozen_Headers = '6' */
				width_fudge='2.2'
				/* default_column_width='3' */
				Autofilter = 'yes'
				absolute_column_width= '5,4,3,7,7,5,3,3,7,7,2,7,2,3,4' 
				);
				options missing=' ';
				TITLE1 font=calibri h=5 "TERMINALI IN DOTAZIONE ALL'AGENZIA";
				/*TITLE2 '(Richieste totali pervenute - Interazioni con operatore)'; */

				proc report nowd data=WORKUSI.OUT_TEMP1;
				COLUMN 
				REGION KEYATM COD_AG AGENZIA DESCRIZIONE_PRESSO TIPO_UBICAZIONE TIPOLOGIA_TERMINALE MODELLO_TERMINALE INDIRIZZO   
				COMUNE PRO REGIONE CAP BARCODE_SI_NO DATA_ATTIVAZIONE_BARCODE;
				define DATA_ATTIVAZIONE_BARCODE / FORMAT=DDMMYY10.;
				define KEYATM / FORMAT=$15.; 
				run;
				ODS TAGSETS.EXCELXP CLOSE;

The problem is the column KEYATM:

define KEYATM / FORMAT=$15.;

in the file xml i have a number format and not text

errore.JPG

 

What is the problem ? I need text 020082230007073 ....

Thanks!!!!


Accepted Solutions
Solution
‎07-13-2016 10:26 AM
Super User
Super User
Posts: 7,430

Re: TAGSETS.EXCELXP (format problem in proc report)

What you are generating is XML, Excel is then interpreting this XML upon opening an rendering it as it sees fit - as no format is specified in the XML, Excel says look a number, let me be helpfull and display as a number.  To get round this you need to supply the XML with the Excel format for that column.  Here is a document which comes up first on the search and explains it:

http://support.sas.com/resources/papers/proceedings13/316-2013.pdf

View solution in original post


All Replies
Solution
‎07-13-2016 10:26 AM
Super User
Super User
Posts: 7,430

Re: TAGSETS.EXCELXP (format problem in proc report)

What you are generating is XML, Excel is then interpreting this XML upon opening an rendering it as it sees fit - as no format is specified in the XML, Excel says look a number, let me be helpfull and display as a number.  To get round this you need to supply the XML with the Excel format for that column.  Here is a document which comes up first on the search and explains it:

http://support.sas.com/resources/papers/proceedings13/316-2013.pdf

Contributor
Posts: 51

Re: TAGSETS.EXCELXP (format problem in proc report)

Thanks

define KEYATM / STYLE( column )= { TAGATTR='format:text'};
Contributor
Posts: 51

Re: TAGSETS.EXCELXP (format problem in proc report)

can I not see this message at every export ?

DOWLOAD.JPG 
Super User
Super User
Posts: 7,430

Re: TAGSETS.EXCELXP (format problem in proc report)

I can't see where you are running that so I can only guess here.  There is an option:

options noresults;

 

Or

 

options results;

 

The first will turn off the feature which automatically opens any output your program creates - so put that in your program before you create the file. 

SAS Super FREQ
Posts: 8,744

Re: TAGSETS.EXCELXP (format problem in proc report)

Try going to Tools-->Options --> Preferences and turn OFF the box for "View results as they are generated" that may prevent the File Download window from popping open.

If you are running SAS on a server, there may not be a way to avoid the File Download messages because if you were, for example running SAS on Unix (which doesn't have Excel) then it makes sense that you would have to download the file from the Unix server to your Windows machine to be opened. But at least with the Tools-->Options --> Preferences setting, you can keep the window from continually popping up as each file gets created.

cynthia
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 322 views
  • 1 like
  • 3 in conversation