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 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;
@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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: