topic Re: Sum with criteria on different dates in New SAS User

Sum with criteria on different dates

cmemtsa — Thu, 24 Jan 2019 13:27:15 GMT

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!

Re: Sum with criteria on different dates

karolis_b — Thu, 24 Jan 2019 13:41:50 GMT

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

Re: Sum with criteria on different dates

cmemtsa — Thu, 24 Jan 2019 14:07:19 GMT

It worked!

Thanks!

Re: Sum with criteria on different dates

VDD — Thu, 24 Jan 2019 14:16:32 GMT

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.

Re: Sum with criteria on different dates

Kurt_Bremser — Thu, 24 Jan 2019 14:17:13 GMT

Please mark @karolis_b 's answer as the solution.

Re: Sum with criteria on different dates

cmemtsa — Mon, 28 Jan 2019 12:19:46 GMT

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.