topic Delete Duplicate data in New SAS User

Delete Duplicate data

dapenDaniel — Fri, 23 Nov 2018 04:36:36 GMT

Hello. I have a data set as following below.

ID Year Var1

1 1999 aa

2 1998 ab

2 1998 ac

2 1998 ab

2 1998 ac

3 1998 ad

The red lines are the same and the blue line are the same. If all three variables (ID, year and Var1) are the same, then retain the first one and delete the duplicated one.

The data set following below is what I expect

ID Year Var1

1 1999 aa

2 1998 ab

2 1998 ac

3 1998 ad

What code do I need? Thanks!

Re: Delete Duplicate data

novinosrin — Fri, 23 Nov 2018 04:43:30 GMT


data have;
input ID              Year              Var1 $;
cards;
1               1999                aa
2               1998                ab
2               1998                ac   
2               1998                ab
2               1998                ac
3               1998                ad
;

proc sql;
create table want as
select distinct *
from have;
quit;

Re: Delete Duplicate data

dapenDaniel — Fri, 23 Nov 2018 04:50:54 GMT

Thank you.It works well!

Re: Delete Duplicate data

dapenDaniel — Fri, 23 Nov 2018 05:02:45 GMT

If I have 4 variables and want to retain the first one for those that are same for the three variables (ID, Year and Var1), what code do I need? I have not used SQL before and am still learning it. Thanks for your patience.

original Data set:

ID Year Var1 Var2

1 1999 aa ba

2 1998 ab bb

2 1998 ac bc

2 1998 ab bd

2 1998 ac be

3 1998 ad bf

Expected Data set:

ID Year Var1 Var2

1 1999 aa ba

2 1998 ab bb

2 1998 ac bc

3 1998 ad bf

Re: Delete Duplicate data

novinosrin — Fri, 23 Nov 2018 05:11:18 GMT

data have;
input ID              Year      var1 $        Var2 $;
cards;
1               1999                aa        ba
2               1998                ab        bb
2               1998                ac        bc
2               1998                ab        bd
2               1998                ac        be
3               1998                ad        bf
;
proc sort data=have out=want nodupkey;
by id year var1;
run;

Just a simple proc sort

Re: Delete Duplicate data

dapenDaniel — Fri, 23 Nov 2018 05:12:21 GMT

Thank you so much! Have a great thanksgiving day!

Re: Delete Duplicate data

novinosrin — Fri, 23 Nov 2018 05:13:18 GMT

You too. Enjoy black friday shopping