topic Re: PROC SQL in SAS Procedures

PROC SQL

yonib — Tue, 14 Oct 2008 12:48:42 GMT

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,

Re: PROC SQL

LinusH — Tue, 14 Oct 2008 17:11:55 GMT

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

Re: PROC SQL

deleted_user — Tue, 14 Oct 2008 17:48:18 GMT

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

Re: PROC SQL

yonib — Tue, 14 Oct 2008 21:11:49 GMT

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!!!