BookmarkSubscribeRSS Feed
InfoAlisaA
Calcite | Level 5

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

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

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.

InfoAlisaA
Calcite | Level 5

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

PGStats
Opal | Level 21

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
Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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
Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 5331 views
  • 1 like
  • 6 in conversation