topic Re: Top Record Extraction based on multiple fields. in SAS Programming

Top Record Extraction based on multiple fields.

anweinbe — Wed, 19 Aug 2020 23:43:33 GMT

I have data that I sorted by Company Year and Salary Descending.

I want to extract only the top 2 salary records for each company and year to a new table.

My data looks like:

Company Year Salary

1 2020 10,000

1 2020 5,000

1 2020 3,000

1 2020 2,000

1 2019 9,000

1 2019 5,000

1 2019 3,000

2 2020 8,000

2 2020 5,000

2 2020 3,000

My desired outcome is my new table will only have the records for 10,000 and 5,000 for company 1 2020, 9,000 and 5,000 for company 1 for 2019, and 8,000 and 5,000 for company 2 in 2020

I tried the following:

proc sort data=temp.own2_for_sample3; by company Year descending salary;
run;

data temp.own2_for_sample3; set temp.own2_for_sample3;
by company Year salary;
if first.year then rnk=0;
rnk +1;
run;

It appears to work.... however I am afraid that since I'm only doing first.year and not including the first.company it could mess up. Any easy modifications?

Thank you!

Re: Top Record Extraction based on multiple fields.

novinosrin — Wed, 19 Aug 2020 23:46:55 GMT

data want;
 do _n_=1 by 1 until(last.year);
  set have;
  by company  year;
  if _n_<=2 then output;
 end;
run;

Re: Top Record Extraction based on multiple fields.

novinosrin — Wed, 19 Aug 2020 23:48:05 GMT

Actually your solution is neat and simple

Re: Top Record Extraction based on multiple fields.

anweinbe — Wed, 19 Aug 2020 23:51:06 GMT

To solve for my issue... do you think if I do If last.year OR if last.company then rnk=0?

Re: Top Record Extraction based on multiple fields.

novinosrin — Wed, 19 Aug 2020 23:53:28 GMT

I modified yours-

proc sort data=temp.own2_for_sample3; 
by company Year descending salary;
run;

data temp.own2_for_sample3; 
set temp.own2_for_sample3;
by company Year ;
if first.year then rnk=0;
rnk +1;
if rnk<=2;
run;

Re: Top Record Extraction based on multiple fields.

anweinbe — Wed, 19 Aug 2020 23:56:15 GMT

My worry is that I could have a situation where the company changes but not the year.

Would this fix that worry? I think it does....

if first._year or first.company then rnk=0;

Re: Top Record Extraction based on multiple fields.

mkeintz — Thu, 20 Aug 2020 19:38:23 GMT

You know about first.year, so consider also using lag(first.year):

proc sort data=temp.own2_for_sample3;
  by company Year descending salary;
run;

data temp.own2_for_sample3; 
   set temp.own2_for_sample3;
   by company Year ;
   if first.year or lag(first.year);
   rnk=first.year+2*lag(first.year);  /* Editted in the "2*" factor */
run;

Re: Top Record Extraction based on multiple fields.

novinosrin — Thu, 20 Aug 2020 00:06:17 GMT

Basically you could have-

1. N companies

2. You need to understand the SORT mechanics

3. when you sort by Company year , you end up having unique combinations of company and year forming its own group

4. Your descending Salary would order the salaries for each unique group of company-year.

5. Now, You want to write the 1st 2 records for each of these unique Company-year combination.

6. Your rnk counter safely increments for each record of the company-year combination

7. All you need is to apply a filter to restrict the resuts to a count of 2 or less i.e. <=2 and then you should have your results?

Does the above make sense?