First, last and where clauses in proc sql

[Tom]

---

@SASdevAnneMarie wrote:
I would like to summuriez the MT_BRUT_CIE by NO_POLICE.
If the D_EFFET (by NO_POLICE) is different I need to substract the most recent MT_BRUT_CIE from the sum.

---

But what is the meaning of doing that final subtraction?

Sounds like you are saying if there is only one date you want the total, but if there is two more dates you want the total without the last date.

What happens if there are multiple records on the last date?  Do you want to subtract all of the values?

Do you just want the collapsed total per NO_POLICE?  Or do you want the total re-merged back onto all of the detailed records?

Do you want the modified total re-merged onto all of the detail records?  Or do you just want the value of that total variable to be different on the last observation.

Do the D_EFFET variables ever have time of day part? Or are they really just a date value that happens to be stored as datetime values?  Perhaps you pulled it from a database like ORACLE or EXCEL that does not really have Date or Time types, just DATETIME?

What would be results of for this data?

GROUP  DATE AMOUNT
1 2021-01-01 100
1 2021-01-02 200
1 2021-01-03 300
2 2021-01-01 100
3 2021-01-01 100
3 2021-01-02 200
3 2021-01-02 300

[SASdevAnneMarie]

But what is the meaning of doing that final subtraction?

Sounds like you are saying if there is only one date you want the total, but if there is two more dates you want the total without the last date. 

- I want the total without the observation from last day.

What happens if there are multiple records on the last date?  Do you want to subtract all of the values?

-Yes, all the values.

Do you just want the collapsed total per NO_POLICE?  Or do you want the total re-merged back onto all of the detailed records?

- Total collapsed without remerging.

Do you want the modified total re-merged onto all of the detail records?  Or do you just want the value of that total variable to be different on the last observation.

- I need this result :

My code is correct, but I can't hold the case when I have, fro example, 3 observation with same DATES, I have this :

I need to have 47506,40 in montant2.

Do the D_EFFET variables ever have time of day part? Or are they really just a date value that happens to be stored as datetime values?  Perhaps you pulled it from a database like ORACLE or EXCEL that does not really have Date or Time types, just DATETIME? Sorry, I didn't understant the question.

My actual code is :

proc sql;
	create table test as select distinct count(distinct(a.D_EFFET)) as nombre,a.NO_POLICE, a.MT_BRUT_CIE,
		sum(abs(a.MT_BRUT_CIE)) as montant,sum(abs(a.MT_BRUT_CIE))-abs(a.MT_BRUT_CIE) as montant2, max(datepart(a.D_EFFET)) as date_recente format ddmmyy10., datepart(a.D_EFFET) as date format ddmmyy10.
	from PRESTATIONS2 as a
 having  max(datepart(a.D_EFFET))=datepart(a.D_EFFET)
;
quit;

Thank you !

[Tom]

So if this is only one day you want the total to be zero?

[Tom]

It really helps to build test data that exercises the boundary conditions of your logic.

Here is data for three groups.  One with multiple days and multiple observations on the last day.  Another with multiple days and a single observations on the last day.  One with just a single observation.

data have;
  input no_police :$20. d_effet :datetime. mt_brut_cie ;
  format d_effet datetime19.;
cards;
1 01JAN2020:00:00 100
1 02JAN2020:00:00 100
1 03JAN2020:00:00 100
1 03JAN2020:00:00 100
1 03JAN2020:00:00 100
2 01JAN2020:00:00 100
2 02JAN2020:00:00 100
2 03JAN2020:00:00 100
3 03JAN2020:00:00 100
;

If I understand your rules then you want this output for those three cases:

Obs    no_police    total

 1         1         200
 2         2         200
 3         3           0

Here is code that gets that result that only using a single SQL statement (a complex one, but just one).

proc sql;
create table want as
  select no_police
       , min(grand_total - case when (date=lastdate) then day_total else 0 end) as total
  from
    (
      select *
           , sum(day_total) as grand_total
           , max(date) as lastdate format=yymmdd10.
      from
      (
        select no_police,datepart(d_effet) as date format=yymmdd10. 
             , sum(abs(mt_brut_cie)) as day_total
          from have
          group by no_police,date
      )
      group by no_police
    )
  group by no_police
;
quit;

Working from the inside out:
Calculate the total per group per day.

Merge back on the grand total and the date of the last day per group.

Finally calculate the results of subtracting the last day's total from the grand total.

[SASdevAnneMarie]

Thank you, Tom !

The first line is right but unfortunately I have 0, when the dates are same :

When the date are same I summirize without substraction, the result for T23900399 is 47506,40

I joined the tested data.

The code is :

proc sql;
create table want as
  select no_police
       , min(grand_total - case when (date=lastdate) then total_par_police else 0 end) as total
  from
    (
      select *
           , sum(total_par_police) as grand_total
           , max(date) as lastdate format=yymmdd10.
      from
      (
        select no_police,datepart(d_effet) as date format=yymmdd10. 
             , sum(abs(mt_brut_cie)) as total_par_police
          from PRESTATIONS2
          group by no_police,date
      )
      group by no_police
    )
  group by no_police
;
quit;

Thank you for your help !

[Tom · Accepted solution]

I still cannot figure out what MEANING the number you are trying to calculate could possibly have.

If you want to not subtract the last day's total when there is only one day then just update the CASE statement to say that.

One way to be able to test that is to also calculate the minimum date per group.

create table want as
  select no_police
       , min(grand_total - 
          case when (firstdate=lastdate) then 0
               when (date=lastdate) then day_total 
               else 0 
          end
            ) as total
  from
    (
      select *
           , sum(day_total) as grand_total
           , max(date) as lastdate format=yymmdd10.
           , min(date) as firstdate format=yymmdd10.
      from
      (
        select no_police,datepart(d_effet) as date format=yymmdd10. 
             , sum(abs(mt_brut_cie)) as day_total
          from have
          group by no_police,date
      )
      group by no_police
    )
  group by no_police
;

[SASdevAnneMarie]

Thank you, Tom,
I would like to sum the amount by police but I must substrat the Most Recent amount (so I must analyse the Different Date). Sometimes there is only ONE date with different amount, in this case I do just sum. Thank you very much for your help!

[Tom]

---

@SASdevAnneMarie wrote:
Thank you, Tom,
I would like to sum the amount by police but I must substrat the Most Recent amount (so I must analyse the Different Date). Sometimes there is only ONE date with different amount, in this case I do just sum. Thank you very much for your help!

---

You should probably keep a variable (like the NOMBRE you calculated) that indicates whether or not the last day's value was subtracted  That way you can distinguish the apples from the oranges in your resulting dataset.

data want;
  set summary;
  by no_police;
  if first.no_police then do; days=0; total=0 ; end;
  days+1;
  total + abs(mt_brut_cie);
  if last.no_police;
  if days > 1 then total=total - abs(mt_brut_cie);
  keep no_police days total;
run;

Obs    NO_POLICE               days      total

 1     I99300001                 3     913137.23
 2     T23900399                 1      47506.40

[Tom]

It is probably a lot more straight forward to abandon trying to twist PROC SQL into a pretzel so it can be used to make this calculation.

Just use normal SAS code instead.

For example you can use PROC SUMMARY to collapse to one observation per group per day. (Use DTDATE format to insure different times of day are treated as the same date.)

Then it is simple in a data step to implement your "subtract last day from the total unless there is only one day" rule.

proc summary nway data="c:\downloads\prestations2";
  class no_police d_effet;
  var mt_brut_cie ;
  output out=summary sum= ;
  format d_effet dtdate9.;
run;

data want;
  set summary;
  by no_police;
  if first.no_police then total=0 ;
  total + abs(mt_brut_cie);
  if last.no_police;
  if not first.no_police then total=total - abs(mt_brut_cie);
  keep no_police total;
run;

Results

Obs    NO_POLICE                 total

 1     I99300001               913137.23
 2     T23900399                47506.40

[SASdevAnneMarie]

Thank you, Tom

Yes, I know, it much better, but I need to imagine the sql code to rewrite it in sql ADODB VBA (with recordset I mean).

Thank you !

[Tom]

---

@SASdevAnneMarie wrote:
Thank you, Tom

Yes, I know, it much better, but I need to imagine the sql code to rewrite it in sql ADODB VBA (with recordset I mean).

Thank you !

---

If you are going to run the SQL in some other dialect then you will probably need to do the remerge of the FIRSTDATE and LASTDATE values yourself.  Most other SQL implementations will not automatically re-merge the summary statistics onto detailed records the way that PROC SQL does.

[SASdevAnneMarie]

I improve my code, but I don't know how to manage the case with the dates are same :

I added updated data.

Thank you very much for your help !

proc sql;
	create table test as select distinct count(distinct(a.D_EFFET)) as nombre,a.NO_POLICE, a.MT_BRUT_CIE,
		sum(abs(a.MT_BRUT_CIE)) as montant,sum(abs(a.MT_BRUT_CIE))-abs(a.MT_BRUT_CIE) as montant2, max(datepart(a.D_EFFET)) as date_recente format ddmmyy10., datepart(a.D_EFFET) as date format ddmmyy10.
	from PRESTATIONS2 as a
 having  max(datepart(a.D_EFFET))=datepart(a.D_EFFET)
;
quit;

[Sajid01]

Hello @SASdevAnneMarie 

I have analysed your requirement and the code I am giving below provides the result what you wanted.
First My analysis of your issue
What you wanted was
1.I would like to make the sum on my MT_BRUT_CIE column, and, if I have the different dates for the MT_BRUT_CIE I would like to subtract the most recent MT_BRUT_CIE.
2. As I have understood your requirements, you had issue running the following
/*cumul_vers-(abs(a.MT_BRUT_CIE) where date=datepart(a.D_EFFET)) and nombre>1*/
3.You had further explained that
"I would like to sum the MT_BRUT_CIE and subtract the most recent MT_BRUTE_CIE (the date is 20FEB2018, the amount is 100,000. I would like to do 1013137,23-100,000. I have also created the column Nombre, because some times the D_EFFET is same, in this case I don’t perform the substrate, only the sum."
4.Please note I have created the variable diff to give sum(abs(MT_BRUT_CIE))-abs(MT_BRUT_CIE ).
This is essentially the same as cumul_vers-(abs(a.MT_BRUT_CIE).

5.The code is given here. The order of variables may not be the same or I may have omitted some but you can modify the code to suit your needs.

proc sql;
create table test (drop=date_diff) as 
select *, count(distinct(D_EFFET)) as nombre,
datepart(D_EFFET) -max(datepart(D_EFFET)) as date_diff,
sum(abs(MT_BRUT_CIE))-abs(MT_BRUT_CIE ) as diff,date
from PRESTA 
where nombre > 1
group by NO_POLICE
having date_diff=0;
quit;

6. The output is below.

Please let me know if you have any questions.