The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

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

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


Accepted Solutions
Solution
‎08-25-2017 04:31 AM
Super User
Posts: 12,994

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

Posted in reply to Nasser_alfea

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.

View solution in original post


All Replies
Super User
Posts: 22,818

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

Posted in reply to Nasser_alfea

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...

Frequent Contributor
Posts: 111

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

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.

Super User
Posts: 22,818

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

Posted in reply to Nasser_alfea

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.

Solution
‎08-25-2017 04:31 AM
Super User
Posts: 12,994

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

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 111

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

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" ???

Frequent Contributor
Posts: 111

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

Posted in reply to Nasser_alfea

It works !

column RAC Groupe GENERATION production ANNEE_AMO , CRD_GEN_MENS_MEAN

the comma was missing

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 382 views
  • 0 likes
  • 3 in conversation