Help using Base SAS procedures

PROC SQL

Reply
SAS Employee
Posts: 105

PROC SQL

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,
Super User
Posts: 5,437

Re: PROC SQL

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
Data never sleeps
N/A
Posts: 0

Re: PROC SQL

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

Re: PROC SQL

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!!!
Ask a Question
Discussion stats
  • 3 replies
  • 139 views
  • 0 likes
  • 3 in conversation