topic Re: First, last and where clauses in proc sql in SAS Programming

First, last and where clauses in proc sql

SASdevAnneMarie — Sat, 04 Sep 2021 19:22:53 GMT

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 !
 
 

Re: First, last and where clauses in proc sql

tarheel13 — Sat, 04 Sep 2021 19:15:20 GMT

We can't see the green.

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sat, 04 Sep 2021 19:20:06 GMT

The line between the « * » in my code.

Re: First, last and where clauses in proc sql

sbxkoenk — Sat, 04 Sep 2021 20:09:24 GMT

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

Re: First, last and where clauses in proc sql

tarheel13 — Sat, 04 Sep 2021 20:13:24 GMT

What does it say in the log?

Re: First, last and where clauses in proc sql

Tom — Sat, 04 Sep 2021 20:33:30 GMT

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?

Re: First, last and where clauses in proc sql

Tom — Sat, 04 Sep 2021 20:35:02 GMT

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?

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sat, 04 Sep 2021 21:39:50 GMT

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.

Re: First, last and where clauses in proc sql

Tom — Sat, 04 Sep 2021 22:18:35 GMT

@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

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sun, 05 Sep 2021 09:24:15 GMT

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 !

Re: First, last and where clauses in proc sql

Kurt_Bremser — Sun, 05 Sep 2021 11:49:06 GMT

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?

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sun, 05 Sep 2021 12:01:33 GMT

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

Re: First, last and where clauses in proc sql

Tom — Sun, 05 Sep 2021 15:12:14 GMT

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.

Re: First, last and where clauses in proc sql

Tom — Sun, 05 Sep 2021 15:22:21 GMT

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()?

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sun, 05 Sep 2021 15:36:46 GMT

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.

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sun, 05 Sep 2021 15:45:01 GMT

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;

Re: First, last and where clauses in proc sql

Tom — Sun, 05 Sep 2021 15:46:42 GMT

@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

Re: First, last and where clauses in proc sql

SASdevAnneMarie — Sun, 05 Sep 2021 15:59:33 GMT

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.

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 !

Re: First, last and where clauses in proc sql

Tom — Sun, 05 Sep 2021 16:52:32 GMT

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

Re: First, last and where clauses in proc sql

Tom — Sun, 05 Sep 2021 17:04:38 GMT

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.