topic Re: Proc Tabulate sorting issue in SAS Programming

Proc Tabulate sorting issue

BillB007 — Fri, 26 Feb 2016 15:23:32 GMT

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

Re: Proc Tabulate sorting issue

Reeza — Fri, 26 Feb 2016 15:30:26 GMT

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;

Re: Proc Tabulate sorting issue

BillB007 — Fri, 26 Feb 2016 18:52:23 GMT

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