PROC SQL

[yonib]

HI all,

l have a problem that i succeed doing it with sas code but i wondering if its possible doing it with sql;

for example:
data temp;
input x;
cards;
1
1
1
2
2
3
3
3
3
;
run;
i want my output be like:
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4
......
Thanks in advance,

[LinusH]

Well, I guess it can be done. Is this a real world problem? Nevertheless, I was a bit curious too, so this is what I came up with (given your input data):

proc sql;
select temp.x, monotonic() as m, calculated m +1 - rowNo as y
from temp inner join
(select x, min(monotonic()) as rowNo
from temp
group by x) as r
on temp.x eq r.x;
quit;

One strange thing is that I had to put the result of the monotonic function in a column before using it in a calculation, otherwise, I got totally different result. Maybe some SAS person can explain that (the monotonic was just until recently unsupported and still pretty undocumented).

But the baseline is that the SQL code isn't especially pretty, and if you are using SAS, the data step will probably a better choice for this problem.

/Linus

[deleted_user]

Thanks! According to the usage note: http://support.sas.com/kb/15/138.html, monotonic() function is unsupported in proc sql.

[yonib]

Thank you Linus ,
I appreciate the time you spend on it !
In sas code its realy not a problem but I was curious if it can be done with sql.
I tried to think about a way to solve it and nothing cross my mind.
Thanks again for your help!!!