DATA Step, Macro, Functions and more

Proc Tabulate sorting issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Proc Tabulate sorting issue

I am importing a lot of Excel files and creating reports using Proc Tabulate.   Many work but some don't.   The errors I get on some of them are similar to this sample:  

 

"ERROR: Data set WORK.REPORT2NEWSORTEDLONG is not sorted in ascending sequence. The current BY group has Assessment Instrument  Name = ARED BS CAPS ST Final Candidate and the next BY group has Assessment Instrument Name = ARED BS CAPS ST Final CT."

 

I think I know why these are considered out of order, because SAS sorts capital letters before lower case ones, but I sorted these using Proc Sort ahead of time.    I also use the option  /ORDER=DATA  in the class statement of Proc Tabulate and I thought that would make the sort order irrelevant.

 

Any ideas why I'm having this problem and more importantly, how to solve it?

relevant SAS code below.  This is all inside of a macro.  The dataset has been re-organized using proc transpose prior to this.

 

proc tabulate data =&input f=4.0 S=[just=c cellwidth=25]; 
var Col1;
class criterion instrument /ORDER=DATA ;
class _name_ ;
table instrument*criterion all={label='Total' s=[just=R background=goldenrod]}*{s=[background=goldenrod]} ,(_name_='Level' ALL)*Col1=" "*(sum="N"*(f=3.0) ROWPCTSUM="%"*f=mypct. ) /box=&reportdate;
BY instrument; 
run;

 

I've tried this on SAS 9.2 and also with SAS 9.4 with the same results.

 

Thanks!

 

Bill

 


Accepted Solutions
Solution
‎02-26-2016 01:52 PM
Super User
Posts: 19,861

Re: Proc Tabulate sorting issue

Please show the sort, the tabulate and then the errors. If you've sorted you shouldn't see the errors, regardless of the case.

 

Order=Data controls the display of the rows, but the BY statement is where you're having issues. BY does require a sort. 

 

If you're confident your data is in a sort order that works for you and is actually sorted in some manner, you can use the NOTSORTED option on your BY statement. Then it treats the data as BY groups but doesn't check for order, but groupings. 

 

BY instrument notsorted;

View solution in original post


All Replies
Solution
‎02-26-2016 01:52 PM
Super User
Posts: 19,861

Re: Proc Tabulate sorting issue

Please show the sort, the tabulate and then the errors. If you've sorted you shouldn't see the errors, regardless of the case.

 

Order=Data controls the display of the rows, but the BY statement is where you're having issues. BY does require a sort. 

 

If you're confident your data is in a sort order that works for you and is actually sorted in some manner, you can use the NOTSORTED option on your BY statement. Then it treats the data as BY groups but doesn't check for order, but groupings. 

 

BY instrument notsorted;
Occasional Contributor
Posts: 15

Re: Proc Tabulate sorting issue

Thanks, that solved the problem.  I whacked myself in the head for not seeing that too!  

 

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 258 views
  • 1 like
  • 2 in conversation