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!
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
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
It worked!
Thanks!
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.