topic Re: Only keep rows with highest value in one column for each unique pair of values in two other colu in SAS Programming

Only keep rows with highest value in one column for each unique pair of values in two other columns?

Wickedestjr — Thu, 25 Jul 2024 14:59:03 GMT

Hello,

I have a dataset like this with hundreds of additional columns and thousands of rows:

First_name | Last_name | Version | Total_payment

Scott | Smith | 1 | $100

John | Smith | 3 | $10

Michael | Scott | 2 | $50

Michael | Scott | 3 | $75

Michael | Scott | 4 | $100

Michael | Jordan | 1 | $20

Michael | Jordan | 2 | $50

I'm looking for a (hopefully) slick way of updating the table so it only includes rows for the latest version of each unique First/Last name combination, like this:

First_name | Last_name | Version | Total_payment

Scott | Smith | 1 | $100

John | Smith | 3 | $10

Michael | Scott | 4 | $100

Michael | Jordan | 2 | $50

The dataset is also not necessarily sorted in any way like I have it presented here. Is there any simple way of achieving this?

Re: Only keep rows with highest value in one column for each unique pair of values in two other colu

ballardw — Thu, 25 Jul 2024 16:20:06 GMT

If by "I'm looking for a (hopefully) slick way of updating the table so it only includes rows for the latest version of each unique First/Last name combination" and "latest" means "last appearing in the data. The a data step with BY group processing:

data have;
   infile datalines dlm='|';
   informat First_name  Last_name $25. Version 4. Total_payment comma10.;
   input First_name  Last_name  Version  Total_payment ;
   format total_payment dollar10.;
datalines;
Scott | Smith | 1 | $100
John | Smith | 3 | $10
Michael | Scott | 2 | $50
Michael | Scott | 3 | $75
Michael | Scott | 4 | $100
Michael | Jordan | 1 | $20
Michael | Jordan | 2 | $50
;

data want;
   set have;
   by First_name  Last_name notsorted;
   if last.last_name;
run;

The NOTSORTED option allows use of BY variables that are not in sort order. When your data is grouped by the BY variables this works as use of a BY statement creates automatic variables that indicate the First or Last of each group, referenced with Last.variable or First.variable. These variables have numeric values of 1 (true) or 0(false). So the subsetting if is only true for last of the last_name values associated with the first_name.

If your data is not already grouped that way then I think you would sort the Have by the name variables plus the Version or other variable that is supposed to have the highest value variable prior to the Want data set.

Note that you did not mention which variable and in your limited example both Version and Total_payment have the "highest value" shown for the wanted output data set.

Re: Only keep rows with highest value in one column for each unique pair of values in two other colu

mkeintz — Thu, 25 Jul 2024 17:50:59 GMT

@Wickedestjr wrote:

Hello,

I have a dataset like this with hundreds of additional columns and thousands of rows:

First_name | Last_name | Version | Total_payment

Scott | Smith | 1 | $100

John | Smith | 3 | $10

Michael | Scott | 2 | $50

Michael | Scott | 3 | $75

Michael | Scott | 4 | $100

Michael | Jordan | 1 | $20

Michael | Jordan | 2 | $50

I'm looking for a (hopefully) slick way of updating the table so it only includes rows for the latest version of each unique First/Last name combination, like this:

First_name | Last_name | Version | Total_payment

Scott | Smith | 1 | $100

John | Smith | 3 | $10

Michael | Scott | 4 | $100

Michael | Jordan | 2 | $50

The dataset is also not necessarily sorted in any way like I have it presented here. Is there any simple way of achieving this?

If the dataset is "not necessarily sorted", I presume that a given firstname/lastname combination is not always presented as consecutive observations, yes? If so, then what do you mean by "latest"? Do you mean the obs with the highest VERSION value? Or do you simply mean the last obs encountered for a given firstname/lastname?

Here's code that keeps the observation with the highest VERSION value:


data _null_;
  if _n_=1 then do;
    if 0 then set have;
    declare hash h (dataset:'have (obs=0)',ordered:'a');
      h.definekey('first_name','last_name');
      h.definedata(all:'Y');
      h.definedone();
  end;
  set have (rename=(version=_vers))  end=end_of_have;
  if h.find()^=0 then call missing(version);
  if _vers>version then do;
     version=_vers;
     h.replace();
  end;
  if end_of_have then h.output(dataset:'want');
run;

If you just want the last obs enountered, then:

data _null_;
  set have;
  declare hash h (dataset:'have',duplicate:'r',ordered:'a');
    h.definekey('first_name','last_name');
    h.definedata(all:'Y');
    h.definedone();
  h.output(dataset:'want');
  stop;
run;

Re: Only keep rows with highest value in one column for each unique pair of values in two other colu

mkeintz — Thu, 25 Jul 2024 18:30:43 GMT

If you are looking for a truly slick way to do this, you can use PROC SUMMARY. The "trick" is to use the ID statement, as below:

proc summary data=have nway;
  class first_name last_name;
  output out=want;
  id version  other...variables...here ;
run;

The ID statement tells proc summary to provide the ID variable with the highest value within each first_name/last_name combination. But instead of a true ID variable you want the highest VERSION value. So put it at the leftmost position in the ID statement, then list all the other variables excluding first_name and last_name.

In your example, there is only one other variable, so you could use:

proc summary data=have nway;
  class first_name last_name;
  output out=want;
  id version  total_payment ;
run;

And if the list of other variables is long, you can use PROC SQL to generate it for you, as a macro variable VARLIST:

proc sql noprint;
  select name 
  into :varlist separated by ' '
  from dictionary.columns  
  where libname='WORK' and memname='HAVE'
       and not lowcase(name) in ('first_name','last_name','version')
  ;
quit;
%put &=varlist;

proc summary data=have nway;
  class first_name last_name;
  output out=want;
  id version &varlist  ;
run;

Re: Only keep rows with highest value in one column for each unique pair of values in two other colu

Wickedestjr — Thu, 01 Aug 2024 19:18:52 GMT

Thank you for sharing this. All the proc summary code is working for me. I'm just having trouble running the proc sql that creates the macro variable VARLIST.

When I run that portion it says "NOTE: No rows were selected." and "WARNING: Apparent symbolic reference VARLIST not resolved."

Do you know why I might be experiencing that?

Re: Only keep rows with highest value in one column for each unique pair of values in two other colu

ballardw — Thu, 01 Aug 2024 21:46:22 GMT

@Wickedestjr wrote:

Thank you for sharing this. All the proc summary code is working for me. I'm just having trouble running the proc sql that creates the macro variable VARLIST.

When I run that portion it says "NOTE: No rows were selected." and "WARNING: Apparent symbolic reference VARLIST not resolved."

Do you know why I might be experiencing that?

Share your code from the log along with all the messages of creating VARLIST.

It is extremely likely that you did not provide the correct name of either the LIBNAME or MEMNAME (data set) those two are stored in the dictionary tables in upper case. So if you used LIBNAME='work' and MEMNAME='somedataset' there were no matches. If those were the names of your library and memname the code would be

LIBNAME='WORK' and MEMNAME='SOMEDATASET'

with no matches for the library and data set name then no variable names would be returned and VARLIST would be empty, nothing to "resolve".

Re: Only keep rows with highest value in one column for each unique pair of values in two other colu

Wickedestjr — Fri, 02 Aug 2024 16:04:16 GMT

I used uppercase for WORK and SOMEDATASET and now that part is working for me too. Thank you!