SAS procedure SUMMARY is a quick method of converting your detail table to a fully summarized one.
Included is a sample. The key option to set is the NWAY - this generates the lowest level of summary for use in the OLAP cube.
Essentially - the class statement contains all the levels used in the various dimensions, the var statement lists all the variables analyzed, and the output statement lists the statistics and new variable names. Other statistics are available - reference the proc MEANS documentation for other output statement options.
proc summary data=sashelp.shoes nway;
class region subsidiary product;
var sales returns;
output out=new sum(sales)=sales_sum max(sales)=sales_max max(returns)=returns_max;
run;
References
5 Comments
What is the difference between detail table and fully summarized table?
You could try the DISTINCTCOUNT() function in a calculated member. I noticed some performance problems with larger cubes, so test.Here is my prior post about that:http://sas-bi.blogspot.com/2009/10/distinctcount-vs-nunique-in-olap-92.html~ Angela
Thank you for your answer. I have used NUNIQUE before, but then the unique count is only available to a certain dimension. My customer would like these unique counts to be available to several dimensions, just like a "normal" measure.
You can still create the cube from the fully summarized table, then you would simply add an NUNIQUE measure for the dimension needed to get the distinct counts.Documentation on the NUNIQUE measure is available here:http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212364.htm~ Angela
Hi,I would like to create a cube on a summarized table, but I can't use proc summary because it doesn't support unique counts. When will the ability to count distinct values and not just N be available when using proc summary?