ODS TAGSETS.EXCELXP proc report year are sorted desc instead of asc in batch mode

[Nasser_DRMCP]

hello,

	proc transpose data= BEH_AGG_5 out= BEH_AGG_5_CRDGEN_TRSPS (drop = _name_) ;
	by RAC &v_AXE. GENERATION PRODUCTION ;
	var CRD_GEN_MENS_MEAN ;
	id ANNEE_AMO ;
	idlabel ANNEE_AMO_LABEL ;
	run ;

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

	ODS TAGSETS.EXCELXP options ( skip_space='1,0,0,0,0' sheet_name='Encours CRD GEN' ) ;
	PROC REPORT DATA = work.BEH_AGG_5_CRDGEN_TRSPS NOwd  ;
	title ;
	format _numeric_ z12.5 ;
  	RUN ;

ODS TAGSETS.EXCELXP CLOSE ;

by launching this code in sas EG environment , it works as intended, year are sorted ascending , 2015, 2016, 2017 (within the excel file) but in bacth mode, years are sorted descending . how can I sort It ?

thanks a lot in advance

Nasser

[Reeza]

In PROC REPORT you can set the order to Descending/Ascending in the DEFINE statement. Ascending should be the default but you haven't specified it as an order variable either. 

I think explicitly setting should remove that issue.

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#p0wy1vqwvz43uhn1g77eb...

[Nasser_DRMCP]

thanks a lot Reeza but I do not understand because "define" within proc report is usefull to sort data.

my need is to sort columns. 2015, 2016 and 2017 are columns names.
before, It was values of "year" column but I need to transpose to get one column for each year in excel. sorry I my issue was not clearly described.

[Reeza]

Ok, based on the new information provided, the answer is you're using PROC REPORT in a way that's not really intended. 

More like a PROC PRINT.

You can control the order of variables by declaring them and using them in DEFINE statements. 

Because you haven't specified any order, it's a guessing game and sometimes you lose when SAS guesses.

*not the intended usage;
proc report data=sashelp.class nowd;
run;

*intended usage;
proc report data=sashelp.class nowd;
column age sex name weight height;
define age / order group;
define sex / 'Sex' just=c;
define name/'Name';
define weight/'Weight';
define height / 'Height';
run;

 You can specify the order by ordering them in the COLUMN statement.

[ballardw · Accepted solution]

I think that you may be going to extra work. You don't provide example starting data but perhaps if you did we might suggest instead of transposing the data at all that you use the BEH_AGG_5 data set and the variable ANNEE_AMO as an Across variable.

The report procedure has a LOT of flexibility for arranging and calculating values, and Proc Tabulate a different set of features.

Since the whole purpose of your transpose is apparently to but the year value across the top of report use the tool in Report that will do so.

[Nasser_DRMCP]

Hello,
thanks ballard.
I gaveup the proc "transpose" and I use "across" within proc report.

PROC REPORT DATA = work.BEH_AGG_5 nowd  ;
	column RAC Groupe GENERATION production ANNEE_AMO CRD_GEN_MENS_MEAN  ;
	define RAC / group ;
	define Groupe / group ;
	define GENERATION / group ;
	define production / group ;
	define ANNEE_AMO / across  ;
	define CRD_GEN_MENS_MEAN / mean noprint ;
	title ;
  	RUN ;

years are correctly transposed and sorted but values of the indicator CRD_GEN_MENS_MEAN are always "1" ???

[Nasser_DRMCP]

It works !

column RAC Groupe GENERATION production ANNEE_AMO , CRD_GEN_MENS_MEAN

the comma was missing