topic Finding the maximum value of each unique ID while keeping all other columns in SAS Programming

Finding the maximum value of each unique ID while keeping all other columns

corkee — Sun, 09 Jul 2017 06:11:13 GMT

Hi, I am using SAS to try to calculate the maximum value of a variable for each unique person. However, I want to retain the other columns of the dataset for the row that contains that maximum value. I've tried to use PROC SQL to accomplish this, but I have no idea how to make sure that the other columns also transfer over. For example, my dataset has 250,000 rows, but only 32,000 unique individuals. I want to find the maximum date for each unique person, and for the row that contains the maximum, I also want to retain all other columns. This is my code so far:

proc sql;
    create table test as
    select ID, max(date_var) from dataset
    group by 1;
quit;

Any help would be great. Thanks.

Re: Finding the maximum value of each unique ID while keeping all other columns

Reeza — Sun, 09 Jul 2017 07:38:48 GMT

Proc sort and a data step will work easily as well.

Proc sort data=have;
By Id variable;
Run;

Data want;
Set have;
By Id;
If last.id;
Run;

However, if there's the possibility that there may be duplicates of the value SQL is easier. You can use the HAVING clause to keep only the rows of interest.

Proc sql;
Create table want as
Select *, max(variable) as max_value
From have
Group by Id 
Having max(variable)=variable;
Quit;

@corkee wrote:

Hi, I am using SAS to try to calculate the maximum value of a variable for each unique person. However, I want to retain the other columns of the dataset for the row that contains that maximum value. I've tried to use PROC SQL to accomplish this, but I have no idea how to make sure that the other columns also transfer over. For example, my dataset has 250,000 rows, but only 32,000 unique individuals. I want to find the maximum date for each unique person, and for the row that contains the maximum, I also want to retain all other columns. This is my code so far:
proc sql;
    create table test as
    select ID, max(date_var) from dataset
    group by 1;
quit;
Any help would be great. Thanks.

Re: Finding the maximum value of each unique ID while keeping all other columns

Patrick — Sun, 09 Jul 2017 08:00:54 GMT

@corkee

The Proc Sort / Data Step option @Reeza posted will likely perform better than the SQL.

Should it be possible that you have more than one record with a max date_var in an ID then the SQL will return all rows with a max value but the Proc Sort / Data Step will only return one row. Your pick!