Help using Base SAS procedures

alternate of monotonic()

Reply
Super Contributor
Posts: 265

alternate of monotonic()

data test;

input name $ sal;

datalines;

a 8

b 3

c 7

d 2

e 4

f 6

g 9

h 10

i 1

j 5

k 10

l 9

;

run;

proc sql number;

create table test1 as select distinct sal from test order by sal desc;

select * from test where sal in (select distinct sal from test1 where monotonic()<4) order by sal desc;

quit;

as monotonic() dont give real time row number so i need to run 2 queries.

is there any function which give realtime row number. or which updates rownumber accordingly after ordering the data???

Trusted Advisor
Posts: 1,131

Re: alternate of monotonic()

Hi,

you can try with _n_

proc sort data=test;

    by descending sal;

run;

data want;

    set test;

    by descending sal;

    if _n_<=4 then output;

run;

Thanks,

Jagadish

Thanks,
Jag
Super User
Posts: 5,257

Re: alternate of monotonic()

What Jagadishkatam is showing is that there are no real way to do this in one select. You can nest you query on more step, so you don't have to have separate queries.

What is you concern? Simplifying your code? Performance?

Data never sleeps
Super Contributor
Posts: 265

Re: alternate of monotonic()

Dear Sir,

Plz guide me ,, is my logic is correct or i sud need to some more practice on it..

in query i need to find out top 2 sal in every dept. for that i did some work n got result, but i m bit confused on quality of my work. plz guide me.

data anuj;

input dept_id sal;

cards;

1 1000

1 2000

1 3000

1 4000

1 5000

1 6000

1 7000

1 8000

2 1000

2 2000

2 3000

2 4000

2 5000

2 6000

3 1000

3 2000

3 3000

3 4000

3 5000

3 6000

4 1000

4 2000

4 3000

4 4000

4 5000

4 6000

5 1000

5 2000

5 3000

5 4000

5 5000

5 6000

6 1000

6 2000

6 3000

6 4000

6 5000

6 6000

6 7000

7 1000

7 2000

7 3000

8 1000

8 2000

9 1000

9 2000

9 3000

9 4000

9 5000

9 6000

9 7000

9 8000

;

run;

proc sort data=anuj;

by dept_id descending sal;

run;

data test1;

set anuj;

if _n_=1 then do; new_id=dept_id; i=0; end;

if dept_id= new_id and i <2 then do;  i=i+1; output ; end;

else if dept_id NE new_id  then do; new_id=dept_id; i=1;output;end;

retain new_id i;

drop i new_id;

run;

proc print; run;

Trusted Advisor
Posts: 1,131

Re: alternate of monotonic()

Hi

As per the above code your logic is perfectly fine. i tried with first. please check, the output is same.

data test1;

    set anuj;

    retain _n;

    by dept_id descending sal;

    if first.dept_id then _n=1;

    else _n+1;

    if _n<=2 then output;

    drop _n;

run;

Thanks,

Jagadish

Thanks,
Jag
Super Contributor
Posts: 265

Re: alternate of monotonic()

thanks a lot,,, its perfect coding.

thanks for improving me.

Super Contributor
Posts: 265

Re: alternate of monotonic()

hi jagdish, there is lots of way to do this in data step, like using outobs= , using obs option many more..

i was looking like function like below...

proc sql number;

select * from test where sal in select distinct sal from test1 where monotonic()<4  order by sal desc;

quit;

like it so i wud replace monotonic() function with accordingly right function , with the help of that i will get correct result . a function which updates rownumber simultaneously at the time or ordering..

Ask a Question
Discussion stats
  • 6 replies
  • 487 views
  • 3 likes
  • 3 in conversation