topic Re: SAS coding question-how to code average of highest values in SAS Programming

SAS coding question-how to code average of highest values

Amy0223 — Thu, 10 Oct 2019 23:12:20 GMT

Hi, I'm not sure how to code this SAS problem below. Any help is greatly appreciated. Thank you!

Calculate the average of the two highest salaries for employees who have three salaries and average of two salaries if they have only two salaries. Print the names of employees who have the highest average in their department.

         Employee_id   Name gender   years   dept     salary01 salary02  salary03;
		 
                 1 Mitchell, Jane A  f    6     shoe     22,450  23,000  26,600
		 2 Miller, Frances T  f  8  appliance      .     32,500  33,000
		 3 Evans, Richard A  m   9  appliance    42,900  43,900  .
		 4 Fair, Suzanne K  f    3  clothing     29,700  32,900 34,500
		 5 Meyers, Thomas D  m  5  appliance     33,700  34,400  37,000
		 6 Rogers, Steven F  m  3    shoe         27,000  27,800  .
		 7 Anderson, Frank F  m  5  clothing      33,000  35,100  36,000
		10 Baxter, David T  m  2     shoe         23,900    .    31,300
		11 Wood, Brenda L  f  3     clothing      33,000  34,000  35,700
		12 Wheeler, Vickie M  f  7  appliance     31,500  33,200  35,600
		13 Hancock, Sharon T  f  1  clothing      21,000   .     22,500
		14 Looney, Roger M    m  10 appliance     42,900 36,200 37,800
		15 Fry, Marie E     f    6  clothing      29,700 30,500 31,200

Re: SAS question-how to code average of highest values

PaigeMiller — Thu, 10 Oct 2019 18:39:37 GMT

You could use CALL SORTN to make sure that the two highest values are always in variables salary02 and salary03, and then average salary02 and salary03.

Example:

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p0exzteatvicqkn1bzyzs0qaecy1.htm&locale=en

SAS question-how to code average of highest values

PaigeMiller — Thu, 10 Oct 2019 20:04:23 GMT

I don't know why the title was changed by either original author or a moderator, but the title "SAS coding question" is a relatively meaningless title (almost every question in the forum could be titled "SAS coding question"); originally it was "SAS question-how to code average of highest values", a much more meaningful and specific title, that cannot apply to almost every question in the forum.

No one benefits from meaningless titles. Everyone benefits from meaningful titles. Whoever changed the title, please change it back.

Re: SAS coding question-how to code average of highest values

hashman — Thu, 10 Oct 2019 21:01:38 GMT

@Amy0223:

In your input sample set, please include only the variables relevant to the task, as in the sample below.

data have ;                                                                                                                                                                                                                                                     
  input id name $ (salary01-salary03) (:comma.) ;                                                                                                                                                                                                               
  cards ;                                                                                                                                                                                                                                                       
 1  Mitchell  22,450  23,000  26,600                                                                                                                                                                                                                            
 2  Miller      .     32,500  33,000                                                                                                                                                                                                                            
 3  Evans     42,900  43,900    .                                                                                                                                                                                                                               
 4  Fair      29,700  32,900  34,500                                                                                                                                                                                                                            
 5  Meyers    33,700  34,400  37,000                                                                                                                                                                                                                            
 6  Rogers    27,000  27,800    .                                                                                                                                                                                                                               
 7  Anderson  33,000  35,100  36,000                                                                                                                                                                                                                            
10  Baxter    23,900    .     31,300                                                                                                                                                                                                                            
11  Wood      33,000  34,000  35,700                                                                                                                                                                                                                            
12  Wheeler   31,500  33,200  35,600                                                                                                                                                                                                                            
13  Hancock   21,000    .     22,500                                                                                                                                                                                                                            
14  Looney    42,900  36,200  37,800                                                                                                                                                                                                                            
15  Fry       29,700  30,500  31,200                                                                                                                                                                                                                            
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  set have ;                                                                                                                                                                                                                                                    
  mean = mean (largest (1, of salary:), largest (2, of salary:)) ;                                                                                                                                                                                              
run ;

The LARGEST function ignores missing values, so there's no need for any extra "two-three" logic. For the first argument, 1 means "first largest" and 2 means "second largest".

Kind regards

Paul D.

Re: SAS coding question-how to code average of highest values

Amy0223 — Thu, 10 Oct 2019 22:13:30 GMT

Thank you so much! I greatly appreciate your kind help!

Re: SAS coding question-how to code average of highest values

Patrick — Thu, 10 Oct 2019 23:03:43 GMT

@Amy0223

Then may-be mark the answer that helped you the most as solution - and eventually also give likes to answers that were helpful.

By marking an answer as solution your question gets "ticked-off" so people can already see in the overview list that it's resolved. Plus: It's also giving someone the credit for spending time for you and providing a solution.