Help using Base SAS procedures

Sorting using Proc Tabulate

Reply
Frequent Contributor
Posts: 90

Sorting using Proc Tabulate

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

Super Contributor
Posts: 1,636

Re: Sorting using Proc Tabulate

Posted in reply to InfoAlisaA

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.

Frequent Contributor
Posts: 90

Re: Sorting using Proc Tabulate

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 =

3.

So I am not sure if this particular option works.

Thanks,

Alisa

Respected Advisor
Posts: 4,919

Re: Sorting using Proc Tabulate

Posted in reply to InfoAlisaA

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

PG
Super User
Posts: 10,018

Re: Sorting using Proc Tabulate

Posted in reply to InfoAlisaA

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

SAS Super FREQ
Posts: 8,862

Re: Sorting using Proc Tabulate

Posted in reply to InfoAlisaA

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;


want_m_table_first.png
Super User
Posts: 5,496

Re: Sorting using Proc Tabulate

Posted in reply to InfoAlisaA

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.

Ask a Question
Discussion stats
  • 6 replies
  • 1529 views
  • 0 likes
  • 6 in conversation