topic Re: How to sum observation according to multiple conditions in SAS Procedures

How to sum observation according to multiple conditions

yanshuai — Thu, 01 Feb 2018 02:42:57 GMT

Hello,

I have a table such as

ID Sales Year Sales_classification

1 12 2008 4567

1 11 2008 7890

1 13 2008 5678

1 14 2009 4567

1 15 2009 7890

1 16 2009 5678

2 8 2008 4567

2 1 2008 7890

2 3 2008 5678

2 4 2009 4567

2 5 2009 7890

2 6 2009 5678

Condition includes:

With the same ID

With the same YEAR

Sales_classification first digit larger than 6

THEN

sum the SALES meet all the conditions above

This may be done with or without CONDITION proc, there may be several ways and logic to do this.

Would you please help me with it?

Thank you very much!

Re: How to sum observation according to multiple conditions

PGStats — Thu, 01 Feb 2018 03:51:49 GMT

You could do:

proc sql;
create table want as
select ID, year, sum(Sales) as sumSales
from have
where first(cats(Sales_classification)) > "6"
group by id, year;
quit;

Re: How to sum observation according to multiple conditions

yanshuai — Thu, 01 Feb 2018 18:43:42 GMT

OMG

You are awesome! It exactly does what I want. It is so neat and clean.

But the outcome table only shows the observation meet the condition. What if I also want those obs show "0" if do not meet the condition?

Re: How to sum observation according to multiple conditions

PGStats — Thu, 01 Feb 2018 18:55:06 GMT

Then switch to:

proc sql;
create table want as
select 
    ID, 
    year, 
    sum(case when first(cats(Sales_classification)) > "6" 
        then Sales 
        else 0 end) as sumSales
from have
group by id, year;
quit;

Re: How to sum observation according to multiple conditions

yanshuai — Thu, 01 Feb 2018 19:45:59 GMT

Great!

Thank you!