DIF by ID sql

[Emma2021]

I would like to calculate difference within ID. I have a below SAS code, how can I change this to SQL?

data tmp1;

set tmp;

by ID;

dif_day=dif(day);

if first.id then dif_day=.;

run;

data have;
input id day ;
cards;
1 1
1 1
2 4
2 4
2 5
3 3
3 3
3 3
3 4
3 4
;
data want;
set have;
by id;
dif_day=dif(day);
if first.id then dif_day=.;
run;

Thank you.

[ballardw]

There is basically no guarantee that SQL will process records in any given order so the concept of DIF (and closely related LAG) functions is basically a non-starter. I won't say it can't be done but there can be considerably more work needed. Use a data step as that is the correct tool if you need to process data in a given order.

Also SQL will not have anything related to the FIRST. or LAST. functionality.

---

@Emma2021 wrote:

I would like to calculate difference within ID. I have a below SAS code, how can I change this to SQL?

data tmp1;

set tmp;

by ID;

dif_day=dif(day);

if first.id then dif_day=.;

run;

Thank you.

---

[Kurt_Bremser]

If you give us example data in usable form, we might be able to give you an example of SQL code, but it won't look good, and the performance may well be horrible.

[Reeza]

You may want to consider learning PL/SQL if you have Oracle and want similar functionality in SQL.

To do this type of calculation you would need different logic but if you had SAS dates it would be easier.

[Ksharp · Accepted solution]

It is not easy for SQL , you need a index variable to identity a unique row.

data have;
input id day ;
n+1;
cards;
1 1
1 1
2 4
2 4
2 5
3 3
3 3
3 3
3 4
3 4
;

proc sql;
create table want as
select id,day,day-(select day from have where id=a.id and n=a.n-1) as dif
 from have as a;
quit;