I have a data like this.
ID Name Amount
1 Pink(1) $50-----This is Duplicate
1 Pink(2) $50-----This is Duplicate
2 Pink(1) $20
2 Pink(1) $25
3 Pink(1) $10
4 Pink(1) $15
5 Pink(1) $20----This is not duplicate
5 Pink(1) $20 ---This is not duplicate
I am trying to remove the duplicate (Id) 1 which have Pink 1 and 2 but with same amount $50.
Even though the ID 5 seems to be same , that not duplicate.
Pink have 2 categories 1 and 2. If 1 or 2 have same value then its duplciate.Name is a char variable.
Thanks
data have;
input ID Name $ Amount $;
cards;
1 Pink(1) $50
1 Pink(2) $50
2 Pink(1) $20
2 Pink(1) $25
3 Pink(1) $10
4 Pink(1) $15
5 Pink(1) $20
5 Pink(1) $20
;
run;
proc sql;
create table a as
select *
from have
group by id
having count(distinct name) ne 1;
create table b as
select *
from have
group by id
having count(distinct name) eq 1;
quit;
data aa;
set a;
by id Amount;
if first.Amount;
run;
data want;
set aa b;
run;
Use SCAN to pull out that 'second' variable 1/2 that's combined with your name.
Then use PROC SORT to order the data.
You'll likely need a data step instead and use FIRST/LAST processing.
Post what you've tried if you're still having issues.
data have;
input ID Name $ Amount $;
cards;
1 Pink(1) $50
1 Pink(2) $50
2 Pink(1) $20
2 Pink(1) $25
3 Pink(1) $10
4 Pink(1) $15
5 Pink(1) $20
5 Pink(1) $20
;
run;
proc sql;
create table a as
select *
from have
group by id
having count(distinct name) ne 1;
create table b as
select *
from have
group by id
having count(distinct name) eq 1;
quit;
data aa;
set a;
by id Amount;
if first.Amount;
run;
data want;
set aa b;
run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.