cumulative sum, two variables

[Mirou]

Hello, I need to transform this data

sinistre	police	date survenanace	prime 1	prime 2
S1	p1	01/01/2015	100	50
S2	p1	01/01/2015	0	80
S3	p1	20/06/2018	80	20

to this one, 

police	date survenanace	prime 1	prime 2
p1	01/01/2015	100	130
p1	20/06/2018	80	20

In fact, for each "police", i have to sum the claims amount (prime 1 prime 2) of the claims (S1 and S2) which happen in the same date (date survenance), 

Thank you !

[PaigeMiller · Accepted solution]

This is a perfect example of where to use PROC SUMMARY.

proc summary nway data=have;
    class police date;
    var prime1 prime2;
    output out=want sum=;
run;

[Mirou]

thank you !

[PeterClemmensen]

An alternative PROC SQL Approach. I like @PaigeMillers code better though 🙂

data have;
input sinistre $ police $ datesurvenanace:ddmmyy10. prime1 prime2;
format datesurvenanace ddmmyy10.;
datalines;
S1 p1 01/01/2015 100 50
S2 p1 01/01/2015 0 80
S3 p1 20/06/2018 80 20
;

proc sql;
   create table want as
   select police,
          datesurvenanace,
          sum(prime1) as prime1,
          sum(prime2) as prime2
   from have
   group by police, datesurvenanace;
quit;

[PaigeMiller]

---

@PeterClemmensen wrote:

An alternative PROC SQL Approach. I like @PaigeMillers code better though 🙂

---

Thanks. In my opinion, PROC SUMMARY/PROC MEANS is a fundamental tool that every SAS user ought to be familiar with. An advantage over PROC SQL here is that if you take the option NWAY out of my example code, you get a lot more results from PROC SUMMARY easily (and which are meaningful in many situations), which would take a lot more effort to get from SQL.

[PeterClemmensen]

@PaigeMiller Couldn't agree more. I have a habit of going with PROC MEANS. Though the Summary Procedure seems to handle situations like this smoother.

[data_null__]

---

@PeterClemmensen wrote:

@PaigeMiller Couldn't agree more. I have a habit of going with PROC MEANS. Though the Summary Procedure seems to handle situations like this smoother.

---

MEANS and SUMMARY are the same procedure they just have different defaults, like printed output.

PROC SUMMARY is my favorite procedure.  

[ballardw]

---

@PaigeMiller wrote:

---

@PeterClemmensen wrote:

An alternative PROC SQL Approach. I like @PaigeMillers code better though 🙂

---

Thanks. In my opinion, PROC SUMMARY/PROC MEANS is a fundamental tool that every SAS user ought to be familiar with. An advantage over PROC SQL here is that if you take the option NWAY out of my example code, you get a lot more results from PROC SUMMARY easily (and which are meaningful in many situations), which would take a lot more effort to get from SQL.

---

Yes!

One of my projects actually involves enough class variables and levels of those variables that the summary data is larger than the original but that one pass allows me to select combinations of the summarized variables for different audiences resulting in over a couple hundred different report documents customized for each audience without much fuss.