BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ImPrem
Obsidian | Level 7

Hi all,

 

I'm a student learning Base SAS Programming. My trainer gave me an assignment to find out the largest or smallest number from dataset. 

It is easy in excel, we can use "large" function to find out the same. I don't have any clue on Base SAS. Kindly help me out. 

 

Ex:

 

Data EmployeeSalary;

input empid $ name $ salary comma6. ;

cards;

RY4672 arun 57,899

TU5789 Varun 24,769

YI5628 Vikas 68,588

GU5879 Ajay 38,589

FL5728 Shiva 42,699

run;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

@art297 ,

Why not use PROC SORT and pick up the third obs , proc transpose is not efficient .

BTW, Congratulations! you surpass @Reeza ,become No.1 solution sas user.

Since you both come from Canada, Have you both met before ?

View solution in original post

26 REPLIES 26
ImPrem
Obsidian | Level 7

Thank you very much for your quick response. 

 

If you don't mind, kindly help me out with code. 

art297
Opal | Level 21

There is more than one answer. Here is one way it could be used:

Data EmployeeSalary;
  input empid $ name $ salary comma6. ;
  cards;
RY4672 arun 57,899
TU5789 Varun 24,769
YI5628 Vikas 68,588
GU5879 Ajay 38,589
FL5728 Shiva 42,699
run;

data want (keep=salaries third_smallest);
  set EmployeeSalary end=eof;
  array salaries(9999) _temporary_;
  salaries(_n_)=salary;
  if eof then do;
    third_smallest=smallest(3,of salaries(*));
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

ImPrem
Obsidian | Level 7

I'm a begineer (Base SAS). For me, its very difficult to understand the sas code. I heard SAS is very simple tool when compare to Excel. But your code was very difficult to understand. Kindly give me very simple SAS code. 

 

Output needs to be printed using proc print. 

 

Thanks in advance!!! 

art297
Opal | Level 21

Sorry, I don't know of an easy way to do what you want. The code I suggested put all of the salaries in an array and then found the third smallest one. You can use proc print to print the resulting file/answer

 

An alternative, but more complex, is to transpose the file. That is, put all of the salaries on one line. e.g.:

Data EmployeeSalary;
  input empid $ name $ salary comma6. ;
  cards;
RY4672 arun 57,899
TU5789 Varun 24,769
YI5628 Vikas 68,588
GU5879 Ajay 38,589
FL5728 Shiva 42,699
run;

proc transpose data=EmployeeSalary out=wide;
  var salary;
run;

data want(keep=third_smallest);
  set wide;
  third_smallest=smallest(3,of col:);
run;

proc print data=want;
run;

Art, CEO, AnalystFinder.com

 

ImPrem
Obsidian | Level 7

Superb!! Your SAS code is very simple & easy to learn for begineers like me. 

 

Output suppose to print the entire coloumn. I mean "Name Empid & Salary"

 

As well as if replace smallest to largest syntax code is not executing.. help me out. 

 

Thanks in advance!

Ksharp
Super User

@art297 ,

Why not use PROC SORT and pick up the third obs , proc transpose is not efficient .

BTW, Congratulations! you surpass @Reeza ,become No.1 solution sas user.

Since you both come from Canada, Have you both met before ?

ImPrem
Obsidian | Level 7

Wooow. Very simple & superb technique!!! Thanks! 

thisisneeraj
Fluorite | Level 6

Hi All,

This can be easiest solution to find third lowest number in dataset.

 

DATA XY;
INPUT Dept $ Salary;
datalines;
AA 100
AA 400
AA 200
AA 300
BB 222
BB 111
BB 444
BB 333
;
RUN;
PROC SORT DATA=XY ;
BY Dept Salary;RUN;

data xy1;
set XY;
by Dept Salary;
if first.dept=1 then n=0;
n+1;
if n=3 then output;
run;

 

hope this helps.

I am working upon to write the code for third largest as well.

guess, proc sql can do this for third largest easily.

ImPrem
Obsidian | Level 7

Your sas code wont execute the expected output. 

 

Here you go... 

 

proc sort data=EmployeeSalary out=emp1;
by descending sal;  / * keep "by sal"  - lowest 3rd Value /
run;
proc print data=emp1 (firstobs=3 obs=3); 
run;

 

 

Very simple logic dude!!!! 🙂 

thisisneeraj
Fluorite | Level 6

How would you find department wise third highest salary ? 

Your answer will not work in that case... 

Your answer is so much at basic level my friend ...!! 

ImPrem
Obsidian | Level 7

My dear friend @thisisneeraj, Nobody want big copmplicated program to execute small thing. At the end, everyone is looking for final output. 

 

Your program is much compliacted (As per understandiing) to execute this very small logic.

 

As you raised the question, "How would you find department wise third highest salary ? "

 

Here you go.!!!! 

 

data abc;
input name $ dept $ sal comma6. loc $;
cards;
abc HR 32,392 CH
ghs MAR 26,378 MY
gsi HR 62,362 JU
gdi HR 23,379 JK
qim HR 83,368 UK
own HR 82,738 IK
KUW MAR 82,768 OK
;
proc sort data=abc out=xyz;
by dept descending sal;
run;
proc print data=xyz (firstobs=3 obs=3);
var name dept sal loc;
where dept= 'HR';
run;

 

All the best!!!! 

thisisneeraj
Fluorite | Level 6

hi All,

Posting the finally completed answer: 

Lets prepare the data first: 

 

DATA XY;
INPUT Dept $ Salary;
datalines;
AA 100
AA 400
AA 200
AA 300
BB 222
BB 111
BB 444
BB 333
AA 500
AA 600
AA 700
BB 555
BB 666
BB 777
;
RUN;

 

1) For third smallest: 
PROC SORT DATA=XY ;
BY Dept Salary;RUN;


data ThirdSmallest;
set XY;
by Dept Salary;
if first.dept=1 then n=0;
n+1;
if n=3 then output;
run;

 

2) For third highest: 

PROC SORT DATA=XY ;BY Dept descending Salary;RUN;

 

PROC RANK DATA=XY descending OUT=XYRanked ;
var Salary; by dept; ranks SalaryRank;
run;

 

data thirdLargest;
set XYRanked;
if SalaryRank=3;
run;

 

hope this helps .

Thanks a lot .

Please let me know for any more inputs in this regards.

 

 

art297
Opal | Level 21

@Ksharp: I didn't know we were having a contest but, no, I could never keep up with @Reeza. Yes, while we live almost 3,364 km apart, @Reeza and I have met at previous SGFs. As for the transpose solution, it wasn't ,my first choice! I only offered it because @ImPrem asked for an alternative to the one I actually presented.

However, since you mentioned efficiency, the code I originally proposed, runs almost 3 times faster than using sort. I ran a rather simple test:

data weight;
  do i=1 to 1000000;
    weight=ranuni(927);
    output;
  end;
run;

data want;
  set weight end=eof;
  array weights(1000000) _temporary_;
  weights(_n_)=weight;
  if eof then do;
    third_smallest=smallest(3,of weights(*));
    output;
  end;
run;

proc print data=want;
run;

proc sort data=weight out=temp;
by weight;
run;

proc print data=temp (firstobs=3 obs=3); 
run;

@ImPrem: Your final solution has a critical error in it. You sort by descending, thus put the records with the largest values at the top, but then print the 3rd largest and call it the third smallest. However, it was nice to see that you really do appear to be trying to learn SAS.

 

Art, CEO, AnalystFinder.com

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 26 replies
  • 3332 views
  • 11 likes
  • 7 in conversation