First, last and where clauses in proc sql

[SASdevAnneMarie]

Hello Experts,
 
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.
 
My code is :
 
proc sql;
create table test as select a.*,
sum(abs(a.MT_BRUT_CIE))as cumul_vers, count(distinct(a.D_EFFET)) as nombre,
max(datepart(a.D_EFFET)) as date format ddmmyy10.
/*cumul_vers-(abs(a.MT_BRUT_CIE) where date=datepart(a.D_EFFET)) and nombre>1*/
from PRESTA as a
group by NO_POLICE;
quit;
I'm wondering how to rewrite the line between the “*” because it doesn't work.
 
I joined the data.
 
Thank you very much for your help !
 
 

[tarheel13]

We can't see the green. 

[SASdevAnneMarie]

The line between the « * » in my code.

[sbxkoenk]

Hello @SASdevAnneMarie ,

I think it will work if you add the "calculated" keyword, like here:

/*-------------------------------------------------------------------
   Referring to a Calculated Column by Alias
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Range of High and Low Temperatures in Celsius';
      select City, (AvgHigh - 32) * 5/9 as HighC format=5.1, 
                   (AvgLow  - 32) * 5/9 as LowC  format=5.1,
                   (calculated HighC - calculated LowC) as Range format=4.1
   from sql.worldtemps;
/* end of program */

Koen

[tarheel13]

What does it say in the log? 

[Tom]

By the code between the * do you mean this commented out code?

cumul_vers-(abs(a.MT_BRUT_CIE) where date=datepart(a.D_EFFET)) and nombre>1

What do you think that code means?

[Tom]

Is this the example data?

data have;
  input (D_EFFET D_VALO) (:datetime.) NO_POLICE :$20. MT_BRUT_CIE cumul_vers nombre date :ddmmyy. ;
  format D_EFFET D_VALO datetime19. date yymmdd10. ;
cards;
20APR2010:00:00:00 29APR2010:09:02:02 I99000001 -40000 1013137.23 3 02/02/2018
21FEB2013:00:00:00 27FEB2013:09:29:19 I99000001 -873137.23 1013137.23 3 02/02/2018
02FEB2018:00:00:00 20FEB2018:12:02:35 I99000001 -100000 1013137.23 3 02/02/2018
;

What is the expect output from that data?

[SASdevAnneMarie]

Thank you for the message, Tom. 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.

[Tom]

---

@SASdevAnneMarie wrote:
Thank you for the message, Tom. 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.

---

First and Last is not a concept that SQL understands.

Why not use a normal data step instead?

data have;
  input (D_EFFET D_VALO) (:datetime.) NO_POLICE :$20. MT_BRUT_CIE cumul_vers nombre date :ddmmyy. ;
  format D_EFFET D_VALO datetime19. date yymmdd10. ;
cards;
20APR2010:00:00:00 29APR2010:09:02:02 I99000001 -40000 1013137.23 3 02/02/2018
21FEB2013:00:00:00 27FEB2013:09:29:19 I99000001 -873137.23 1013137.23 3 02/02/2018
02FEB2018:00:00:00 20FEB2018:12:02:35 I99000001 -100000 1013137.23 3 02/02/2018
;

proc sort data=have;
  by no_police D_EFFET ;
run;

data want;
  set have;
  by no_police D_EFFET ;
  if first.no_police then do;
    running_total=0;
    d_effet_count=0;
  end;
  d_effet_count+first.d_effet;
  if not last.no_police then running_total + abs(mt_brut_cie);
run;

proc print;
  var no_police d_effet d_effet_count running_total mt_brut_cie ;
run;

Is this what you are expecting?

                                           d_effet_     running_     MT_BRUT_
Obs    NO_POLICE                D_EFFET      count       total          CIE

 1     I99000001     20APR2010:00:00:00        1        40000.00     -40000.00
 2     I99000001     21FEB2013:00:00:00        2       913137.23    -873137.23
 3     I99000001     02FEB2018:00:00:00        3       913137.23    -100000.00

[SASdevAnneMarie]

Thank you, Tom,

My code in 2 steps could be  :

proc sql;
	create table test as select a.*,
		sum(abs(a.MT_BRUT_CIE))as cumul_vers, count(distinct(a.D_EFFET)) as nombre,
		max(datepart(a.D_EFFET)) as date format ddmmyy10.
       /*cumul_vers-(abs(a.MT_BRUT_CIE) where date=datepart(a.D_EFFET)) and nombre>1*/
      from PRESTA as a
	group by NO_POLICE;
quit;

data PRESTA2;
	set test;
    by NO_POLICE;
	if date=datepart(D_EFFET) and nombre>1 then
		do;
		    retain;
			montant=cumul_vers-(abs(MT_BRUT_CIE));
		end;
run;

The result is like :

I'm wondering if I can rewrite it in proc sql sas, I found on Internet this answers  :

When I run this code I have error on top(1)

I need to use the proc sql, because after I rewrite this code on sql adodb in VBA, so it more easy for me to start with the proc sql.

Thank you very much for your help !

[Kurt_Bremser]

if date=datepart(D_EFFET) and nombre>1 then
		do;
		    retain;

RETAIN is a declarative statement to declare variables as retained, which means they are not reset to missing at the start of a data step iteration.

It cannot be executed conditionally, and without a variable name it has no effect at all.

What do you want to retain between data step iterations?

[SASdevAnneMarie]

Thank you, for your answear, Kurt.
It was just the example of the result that I need to get.

I would like to rewrite this 2 steps code on 1 step on proc sql , but I don't know how to do it. 😞

[Tom]

Please explain in words what your 2 step code is doing.

Where is NOMBRE coming from?  What does NOMBRE>1 mean in this case?

The SQL language as originally defined in the 1980's and codified into 1992 standard that PROC SQL supports has no concept of first and last. Other implementations of SQL added extra non-standard features to get around this and ultimately the SQL standard was expanded to at least include windowing functions that allow something like processing records in a ordered manner.

But you don't need them in SAS because SAS has a full blown data processing language of its own called the data step.

[Tom]

Explain in words what you are trying to calculate.

It looks a lot like a running total, but that is not clear at all, and your example does not have enough variation to detect any pattern into what you are trying to do.

It looks like there are only three important variables NO_POLICE, D_EFFET and MT_BRUT_CIE.

You are grouping by NO_POLICE.

You are taking the SUM() of MT_BRUT_CIE (please explain why the ABS() function is needed).

You are counting distinct values of the datetime value of D_EFFET (are there really multiple records for the exact same point in time?).

You are converting D_EFFET from a datetime into a date.  Are there time of day values that will be lost? What happens if there are more than one distinct D_EFFET values on the same day for the same NO_POLICE value?

Why are then sometimes subtracting the value of MT_BRUT_CIE back out of the SUM()?  

[SASdevAnneMarie]

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.