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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: