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
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.
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
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
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
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;
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.
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.
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.