DATA Step, Macro, Functions and more

ODS TAGSETS.EXCELXP proc report : remove an empty line in the header of the exported excel file

Reply
Frequent Contributor
Posts: 78

ODS TAGSETS.EXCELXP proc report : remove an empty line in the header of the exported excel file

Capture empty line.PNG

Hello

 

by executing this code, 2 empty lines appears  in the header (in yellow in the screenshot). I would like to remove them.

thanks a lot for your help

kind regards

Nasser

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

	/* 1er onglet : mois courant sans cumul */
	ODS TAGSETS.EXCELXP options ( skip_space='1,0,0,0,0'  sheet_name='mois courant sans cumul' ) ;
	PROC REPORT DATA = work.T7_MEASURES_M0 (where=(INDEX(Measure,'sans cumul'))) nowd ;
	column AXE_PRODUIT GENERATION measure periode_month_M0  , value ;
	define AXE_PRODUIT / group 'Produit' ;
		COMPUTE AXE_PRODUIT ;
		IF AXE_PRODUIT NE '' then hold_axe_produit = AXE_PRODUIT ;
		IF AXE_PRODUIT EQ '' then AXE_PRODUIT = hold_axe_produit ;
		ENDCOMP ;
	define GENERATION / group 'Génération' ; 
		COMPUTE GENERATION ;
		IF GENERATION NE '' then hold_GENERATION = GENERATION ;
		IF GENERATION EQ '' then GENERATION = hold_GENERATION ;
		ENDCOMP ;
	define measure /  group 'Indicateur' ;
	define periode_month_M0 / across '' ;
	define value / '' ;
		compute value ;
 		if INDEX(measure,'Mont') then do ;
 		call define(_COL_,'FORMAT','12.2') ;
 		end;
 		else call define(_COL_,'FORMAT','percent12.9') ;
 		endcomp ;
  	RUN ;
ODS TAGSETS.EXCELXP CLOSE ;
SAS Super FREQ
Posts: 8,868

Re: ODS TAGSETS.EXCELXP proc report : remove an empty line in the header of the exported excel file

Posted in reply to Nasser_alfea

Hi:

Instead of this:
column AXE_PRODUIT GENERATION measure periode_month_M0,value ;

use one of these options:
Option 1) Change only column statement:
column AXE_PRODUIT GENERATION measure value,periode_month_M0 ;
or
Option 2) Change column statement to move header up on same level as values for periode_month_M0 and also, change define statements:
column ('Produit' AXE_PRODUIT) ('Generation' GENERATION) ('Indicateur' measure) periode_month_M0,value;
define produit / group ' ';
define generation / group ' ';
define measure / ' ';

 

Cynthia

 

Ask a Question
Discussion stats
  • 1 reply
  • 214 views
  • 1 like
  • 2 in conversation