BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stat_sas
Ammonite | Level 13

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Astounding
PROC Star

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

stat_sas
Ammonite | Level 13

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

ballardw
Super User

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.

stat_sas
Ammonite | Level 13

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.

Astounding
PROC Star

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

stat_sas
Ammonite | Level 13

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

Astounding
PROC Star

Perfect.

 

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

 

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

pearsoninst
Pyrite | Level 9
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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1122 views
  • 1 like
  • 4 in conversation