I have the following dataset:
ID |
NAME |
AMOUNT |
251717 |
Better Inc |
$5 |
251717 |
Better Co |
$24 |
252534 |
Pan Holdings |
$520 |
252534 |
Pan Co |
$820 |
259169 |
Province Inc |
$300 |
259992 |
ABC Inc |
$620 |
Is there a way in SAS EG query builder to create a new computed column called NEWNAME that when IDs are the same, to take the Name with the most amount? So the output would look like this following:
ID |
NEWNAME |
AMOUNT |
251717 |
Better Co |
$29 |
252534 |
Pan Co |
$1,340 |
259169 |
Province Inc |
$300 |
259992 |
ABC Inc |
$620 |
Thanks
A little coding will be much easier than fiddling with the query builder:
proc sort data=have;
by id descending amount;
run;
data want;
set have (rename=(name=_name amount=_amount));
by id;
retain name amount;
if first.id
then do;
name = _name;
amount = _amount;
end;
else amount + _amount;
if last.id;
drop _:;
run;
I use EG. You can dabble with the calculator, go to Advanced Functions and mess around with the functions.
Otherwise, Drag the variable column name into select, then press the calculator thing. and identify the variable from its "source column like 't1'. or something like that and make an input statement.
Also, find out if you can make a case statement.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.