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 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;
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.
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;
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?
@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".
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: