ODS TAGSETS.EXCELXP proc print underscore ahead column unintended

[Nasser_DRMCP]

proc transpose data= TAB_AMO_SORT out= TAB_AMO_SORT_TRSPS (drop=_name_)  ;
by MOIS_AMO ;
var MNT_MOIS_AMO_NET ;
id MOIS_PROD ;
run ;

ods results off;

ODS TAGSETS.EXCELXP FILE = "&xlsfile."  style=normal  ;
ODS TAGSETS.EXCELXP
      options ( skip_space='1,0,0,0,0'
	  			embedded_titles='yes'
				sheet_interval='none'
                suppress_bylines='off' 
                width_fudge='0.75'
				sheet_name='Tableau amortissement'
				) ;
	PROC PRINT DATA = work.TAB_AMO_SORT_TRSPS NOOBS ;
	title ;
	format _numeric_ z15.6 ;
  	RUN ;
ODS TAGSETS.EXCELXP CLOSE ;

Hello,

I would like to export a dataset transposed by month into an excel file.

The issue I encounter is : by lauching the prog in sas EI, it works, "yearmonth" column has values like 201601 201602..

But with the batch mode, an underscore appears ahead yearmonth values like _201601

thanks a lot in advance for yout help

Nasser

[Nasser_DRMCP]

not sas EI but sas EG enterprise guide

[RW9]

This is simply a matter of not understanding SAS and using bad habbits instilled by Excel.  Whilst Excel may be good for looking at transposed data, its really not a good data storage method, and using dates as headers is bad.

What is happening is that the proc transpose is adding an underscore as variable names cannot start with a number, however labels can contain anything, so I assume your output is using in one instance labels and in the other variable names.  To fix this I would suggest that you put the date in the label part, and keey the variable names consistent, e.g:

proc transpose data=tab_amo_sort out=tab_amo_sort_trsps (drop=_name_) ;
  by mois_amo;
  var mnt_mois_amo_net;
  idlabel mois_prod;
run ;

ods results off;

ods tagsets.excelxp file="&xlsfile." style=normal;
ods tagsets.excelxp options(skip_space='1,0,0,0,0'
                            embedded_titles='yes'
                    	    sheet_interval='none'
                            suppress_bylines='off' 
                            width_fudge='0.75'
                    	    sheet_name='Tableau amortissement');
title;

proc report data=work.tab_amo_sort_trsps nowd;
  columns _all_;
run;

ods tagsets.excelxp close;

Note the use of idlabel rather than id, and i would also use proc report rather than print as its more flexible.

[ballardw · Accepted solution]

SAS data sets cannot have a variable name that start with any character other than a letter or the underscore character.

You might try adding a variable to your TAB_AMO_SO that has the text you would like to see in the output. Then use that variable in an IDLABEL statement in Proc transpose. This will assign the text as the label for the column variables. If the column names aren't critical perhaps change:

id MOIS_PROD ;

to

idlabel MOIS_PROD ;

The variable names will be col1, col2 but should have the lables you want.

Add the Label option to proc print to use the label as the column heading instead of the name of the variable.

PROC PRINT DATA = work.TAB_AMO_SORT_TRSPS NOOBS  Label;