i had oppurtunity to change bunch of SAS code into DBMS specific sql. I have learned one thing, emulating sort is impossible from SAS to DBMS or vice versa unless you are using whole row as part of your sort. Let me explain through an example.
data have;
input id age col1 col2 col3;
datalines;
1 30 1 1 3
1 29 2 2 3
1 29 1 1 1
1 30 1 1 7
2 40 1 1 1
2 40 1 2 3
3 25 1 5 7
;
proc sort data =have
out=have1;
by id age;
run;
data want;
set have1;
by id;
if first.id then rownum=1;
ELSE rownum+1;
run;
/*output is*/
Obs id age col1 col2 col3 rownum
1 1 29 2 2 3 1
2 1 29 1 1 1 2
3 1 30 1 1 3 3
4 1 30 1 1 7 4
5 2 40 1 1 1 1
6 2 40 1 2 3 2
7 3 25 1 5 7 1
/*I ran the same equivalent code in DBMS(used Postgres)*/
create table have
("id" int,
age int,
col1 int,
col2 int,
col3 int);
insert into have values (1, 30, 1, 1, 3);
insert into have values(1, 29, 2, 2, 3);
insert into have values (1, 29, 1, 1, 1);
insert into have values (1, 30, 1, 1, 7);
insert into have values (2, 40, 1, 1, 1);
insert into have values (2, 40, 1, 2, 3);
insert into have values (3, 25, 1, 5, 7);
select *, row_number() over(partition by id order by age) as rownum from have;
/*output*/
id age col1 col2 col3 rownum
1 29 1 1 1 1
1 29 2 2 3 2
1 30 1 1 7 3
1 30 1 1 3 4
2 40 1 1 1 1
2 40 1 2 3 2
3 25 1 5 7 1
you will see the answer make sense in both code, but both results are not same especially for ID 1. I did not go through your code for other aspects, but just sharing one of my experiences
... View more