topic Re: How to stop automatic ordering for a variable in PROC REPORT in SAS Procedures

How to stop automatic ordering for a variable in PROC REPORT

kingCobra — Thu, 11 Jun 2015 17:25:49 GMT

Hi,

I have this tricky situation: My column statement looks like

column ('___' g CATEG1 p VAR1 n1 n2 n3);

"g" is numeric for sorting the CATEG1.

There is no 1 to 1 correspondence between "g" and CATEG1. For example my final data before PROC REPORT looks like,

g CATEG1 p VAR1 n1 n2 n3

1 xxxx 1

2 yyyy 1

3 s 0.1

3 m 0.2

3 q 0.3

3 b 0.4

4 zzzz 1

My DEFINE statement

define g / order order=internal noprint;

define CATEG1 / order order=internal left "Category" noprint ;

define p / order order=internal noprint;

define VAR1 / order order=data left "Variable" ;

define n1...n3;

I want to see the PROC REPORT output exactly as in the final data. But due to PROC REPORT's internal ordering CATEG1 is getting sorted alphabetically in the output. So I'm getting "b" first and "s" last.

Is there anyway I can switch off this automatic ordering and retain the same ordering as in final dataset?

I tried removing order=internal in the define statement but that is not working.

Any help will be appreciated.

Many Thanks

Re: How to stop automatic ordering for a variable in PROC REPORT

ballardw — Thu, 11 Jun 2015 18:00:55 GMT

Change the Order=internal to order=data to maintain order as appears in the data set.

Without an order= the default is order=formatted which is often very similar to order=internal

Re: How to stop automatic ordering for a variable in PROC REPORT

data_null__ — Thu, 11 Jun 2015 18:50:25 GMT

Looks like you want the order to be by G and P with CATEG1 DISPLAY.

columns G P CATEG1

change DEFINE for CATEG1 to DISPLAY.

What is VAR1 does it need to be order.

Re: How to stop automatic ordering for a variable in PROC REPORT

kingCobra — Thu, 11 Jun 2015 23:34:17 GMT

Thanks for the answers.

Actually Order=data did not work for this case. And although columns G P CATEG1 would do but that is going to alter the ordering of VAR1. What I can do is, introduce another ordering variable corresponding to CATEG1 ( _ord_ = 1,2,3,4). So my column statement is now

Column ('___' g _ord_ CATEG1 p VAR1 n1 n2 n3);

This will preserve the order of CATEG1 as it is in the final data.

I should have framed the question in a different way. My main intention was to know if there is any way/option to stop the automatic ordering of the CATEG1 variable in the above scenario? Something akin to NOTSORTED option in BY statememnt.

Re: How to stop automatic ordering for a variable in PROC REPORT

data_null__ — Fri, 12 Jun 2015 13:16:36 GMT

There is no "AUTOMATIC ORDERING" you specified CATEG1 as ORDER in the DEFINE statement.

If you included proper sample data and described your expectation in more detail you would get better help.