Sum with criteria on different dates

[cmemtsa]

Hi,

I have this dataset

DATE          CD    N      CC
31-Oct-18      1     1       10
31-Oct-18      2              10
31-Oct-18      3      1      10
30-Nov-18     1      1      10
30-Nov-18     2              10
30-Nov-18     3      1      10
31-Dec-18     1              10
31-Dec-18     2      1      10
31-Dec-18     3              10

and I want to sum CC per CD for all months, but only for the CDs that had N=1 at 31-Oct-18 and N null at 31-Dec-18.

The result should be 60 i.e. sum CC for the CD 1 and 3.

How can I write this code?

Thanks!

[karolis_b · Accepted solution]

Hey!

You can try using sql syntax with subquery, where it could look something like that:

proc sql noprint;
   create table result as
   select
      date,
      cd,
      sum(cc) as result
   from input
   where cd in (select distinct cd 
                from input 
                where (date = '31oct2018'd and n = 1) 
                      or (date = '31dec2018'd and n is missing))
   group by date, cd
;quit;

Hope it solves you problem!

- Karolis

[cmemtsa]

It worked!

Thanks!

[VDD]

That is great @cmemtsa that @karolis_b solution works for your request.

Please mark the appropriate solution as an excepted solution so that the tread is closed.

[Kurt_Bremser]

Please mark @karolis_b 's answer as the solution.

[cmemtsa]

Hi,

can I ask you something.If I want to keep the start date fixed and the end date rolling i.e. to have result also for nov2018 when n is missing, how can I do it?

Thanks.