Help using Base SAS procedures

How to stop automatic ordering for a variable in PROC REPORT

Reply
Occasional Contributor
Posts: 15

How to stop automatic ordering for a variable in PROC REPORT

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

Super User
Posts: 10,508

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

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

Respected Advisor
Posts: 3,777

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

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.

Occasional Contributor
Posts: 15

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

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.

Respected Advisor
Posts: 3,777

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 280 views
  • 3 likes
  • 3 in conversation