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.
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;
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.