topic Re: DIF by ID sql in SAS Programming

DIF by ID sql

Emma2021 — Wed, 19 Jan 2022 15:53:58 GMT

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.

Re: DIF by ID sql

ballardw — Wed, 19 Jan 2022 15:43:02 GMT

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.

Re: DIF by ID sql

Kurt_Bremser — Wed, 19 Jan 2022 15:50:06 GMT

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.

Re: DIF by ID sql

Reeza — Wed, 19 Jan 2022 16:09:49 GMT

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.

Re: DIF by ID sql

Ksharp — Thu, 20 Jan 2022 12:39:43 GMT

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;