topic Re: Sorting using Proc Tabulate in SAS Procedures

Sorting using Proc Tabulate

InfoAlisaA — Wed, 25 Apr 2012 23:48:03 GMT

Hello Everyone,

I am not sure where I am going wrong with this code, but I am hoping someone can help me.

So I have this program that sorts my data and then uses a proc tabulate procedure to output the set:

proc sort data=orion.purchased_products out=purchased_products;

by descending Order_Type; /*Sorting the data set by Order_Type*/

run;

proc tabulate data=purchased_products format=comma12.2;

where Supplier_Name contains 'Sports';

by descending Order_Type;

class Customer_Age_Group Supplier_Name;

var Quantity Total_Retail_Price;

table Supplier_Name,

Customer_Age_Group*Total_Retail_Price=' '*sum=' '

/printmiss misstext='$0' box='Total Retail Price';

title 'Products by Sales Supplier and Customer Age Group';

run;

The other half of my assignment is to modify the proc tabulate code so I don't need the proc sort code.

I have tried many things and so far nothing seems to work as well as the tabulate with the proc sort.

Here is my code so far:

proc tabulate data=purchased_products2 format=comma12.2;

where Supplier_Name contains 'Sports';

class Order_Type/mlf descending;

class Customer_Age_Group Supplier_Name;

var Quantity Total_Retail_Price;

table Supplier_Name,

Customer_Age_Group*Total_Retail_Price=' '*sum=' '

/printmiss misstext='$0' box='Total Retail Price';

title 'Products by Sales Supplier and Customer Age Group';

run;

I changed the by into a class and then used /mlf descending to see if that would work, but it's just not producing the same results.

Can someone check my code and let me know where I am going wrong?

Thanks,

Alisa

Re: Sorting using Proc Tabulate

Linlin — Thu, 26 Apr 2012 00:05:40 GMT

try using:

proc tabulate data=purchased_products2 format=comma12.2 order=DATA or FORMATTED or FREQ or UNFORMATTED;

select the option that meets your need.

Re: Sorting using Proc Tabulate

InfoAlisaA — Thu, 26 Apr 2012 00:28:57 GMT

Hi Linlin,

I tried all of those options and all of them produced the same error in the error log:

ERROR: Data set WORK.PURCHASED_PRODUCTS3 is not sorted in descending sequence.

The current BY group has Order Type = 2 and the next BY group has Order Type =

So I am not sure if this particular option works.

Thanks,

Alisa

Re: Sorting using Proc Tabulate

PGStats — Thu, 26 Apr 2012 02:01:22 GMT

What is the mlf option supposed to do for you. Is there a multilabel format associated with Order_type? What if you remove the mlf ? - PG

Re: Sorting using Proc Tabulate

Ksharp — Thu, 26 Apr 2012 03:02:10 GMT

Maybe you need a special option for BY statement.

data class;set sashelp.class;run;

proc tabulate data=class;

by descending sex notsorted;

class age;

var weight ;

table age,weight*sum;run;

Ksharp

Re: Sorting using Proc Tabulate

Cynthia_sas — Thu, 26 Apr 2012 17:02:39 GMT

Hi:

I see that you have DESCENDING on the CLASS statement for ORDER_TYPE, but where do you use ORDER_TYPE in your revised code. You can't use a BY statement without knowing that the data are sorted. So, if you need to create a separate table for each ORDER_TYPE, and you can't sort, you COULD use BY with NOTSORTED, but you do have another alternative, which is to move ORDER_TYPE into the PAGE dimension.

Here's an example of only 10 obs -- but the data are originally in NAME order or some other order. You can use the DESCENDING option on a CLASS statement. Note how I use GENDER, but with the DESCENDING option on the CLASS statement. Then GENDER is specified in the PAGE dimension. By default, TABULATE will make a "bucket" or "bin" for every value of GENDER and then by default, will order the display in ASCENDING order. ORDER=UNFORMATTED is the default value for a CLASS variable.

cynthia

** what if your data set is sort of ordered by NAME and you;
** do not want to or cannot RE-sort by the GENDER variable?;
** then use the PAGE dimension and DESCENDING;
data classname;
infile datalines;
input name $ gender $ age weight;
return;
datalines;
Alexis      F      14     84.0
Bob         M      14     98.0
Carol       F      15     102.5
John        M      14     89.5
Janet       F      15     112.5
Jake        M      15     92.5
Judy        F      15     90.0
Lucas       M      15     117.0
Mary        F      15     102.0
Allan       M      15     112.5
;
run;

ods listing close;
ods html file='c:\temp\order_page_descending_no_by.html' style=sasweb;
proc tabulate data=classname;
title 'using classname want M table first';
class gender / descending ;
class age;
var weight ;
table gender,
        age,
        weight*(n min mean max);
run;
title;
ods html close;

Re: Sorting using Proc Tabulate

Astounding — Thu, 26 Apr 2012 17:56:00 GMT

Alisa,

The general method to get a separate page for each ORDER_TYPE is to use that as a third dimension to the table:

tables ORDER_TYPE,

Supplier_Name,

Customer_Age_Group* ...

It won't be in descending order, but as was noted above, you do have a few choices when it comes to the order.

Good luck.