BookmarkSubscribeRSS Feed
Aman4SAS
Obsidian | Level 7

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

6 REPLIES 6
Jagadishkatam
Amethyst | Level 16

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
LinusH
Tourmaline | Level 20

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
Aman4SAS
Obsidian | Level 7

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;

Jagadishkatam
Amethyst | Level 16

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
Aman4SAS
Obsidian | Level 7

thanks a lot,,, its perfect coding.

thanks for improving me.

Aman4SAS
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1711 views
  • 3 likes
  • 3 in conversation