DATA Step, Macro, Functions and more

Proc Tabulate

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,204
Accepted Solution

Proc Tabulate

Hi Everyone,

 

I need to tabulate sales of some brands something like this

 

                                    sales

Brand A(Excluding 0)    124

Brand B                          250

Brand C                          350

 

Just wondering, is there a way to calculate sum sales for brand "A" excluding zero values, while other brands including everying.

 

Any help regarding this will be highly appreciated.

 

Regards


Accepted Solutions
Solution
‎11-25-2015 02:35 PM
Super User
Posts: 5,069

Re: Proc Tabulate

Perfect.

 

You should be able to use a WHERE statement like this:

 

where (brand ne 'A') or (brand='A' and sales > 0);

View solution in original post


All Replies
Super User
Posts: 5,069

Re: Proc Tabulate

Why will it matter?  The SUM including 0 is the same as the SUM excluding 0.

Trusted Advisor
Posts: 1,204

Re: Proc Tabulate

Thanks  - I am looking for average sales after this step and will exclude zero values in avg calculation

Super User
Posts: 10,460

Re: Proc Tabulate

One way is to use

WHERE sales > 0;

in your code. However the count (denominator) may not be what you want if you are doing anything else in the same procedure call.

Otherwise you may get into some preprocessing.

Trusted Advisor
Posts: 1,204

Re: Proc Tabulate

Thanks -  I just want to change denominator for brand 'A' excluding number of zeros and keep full count in denominatior for other variables in a single run of proc tabulate syntax. I was thinking  a way to adjust this in proc tabulate syntax but seems like I need to do some preprocessing before tabulate.

Super User
Posts: 5,069

Re: Proc Tabulate

It may be possible, but we need to know what your data looks like.  Give us a sample of 10 observations and the key variables (anywhere from 2 to 5 variables, depends on what the data looks like ... just include whatever you think would be relevant).

Trusted Advisor
Posts: 1,204

Re: Proc Tabulate

Please see below a snapshot of the data

 

Brand Province    txn_date    Sales
A  AA       2015-10-01            12
A  AA       2015-10-09            0
B  AA       2015-10-09           25
B  BB       2015-10-22           0
A  CC       2015-10-25          26
A  CC       2015-10-26          0
A  DD       2015-10-26          0
A  EE       2015-10-30          23
B  AA       2015-10-30         15

Solution
‎11-25-2015 02:35 PM
Super User
Posts: 5,069

Re: Proc Tabulate

Perfect.

 

You should be able to use a WHERE statement like this:

 

where (brand ne 'A') or (brand='A' and sales > 0);

Frequent Contributor
Posts: 108

Re: Proc Tabulate

I am not sure if we can , as Astounding said "Why will it matter? " my question is if i need to exclude values less then 5 can we do it in Proc Tabulate.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 259 views
  • 1 like
  • 4 in conversation