topic Re: cumulative sum, two variables in SAS Programming

cumulative sum, two variables

Mirou — Fri, 31 May 2019 13:58:58 GMT

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 !

Re: cumulative sum, two variables

PaigeMiller — Fri, 31 May 2019 14:13:16 GMT

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;

Re: cumulative sum, two variables

PeterClemmensen — Fri, 31 May 2019 14:17:54 GMT

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;

Re: cumulative sum, two variables

PaigeMiller — Fri, 31 May 2019 14:24:35 GMT

@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.

Re: cumulative sum, two variables

PeterClemmensen — Fri, 31 May 2019 14:29:14 GMT

@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.

Re: cumulative sum, two variables

Mirou — Fri, 31 May 2019 14:37:44 GMT

thank you !

Re: cumulative sum, two variables

ballardw — Fri, 31 May 2019 14:49:35 GMT

@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.

Re: cumulative sum, two variables

data_null__ — Fri, 31 May 2019 22:46:59 GMT

@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.