topic Re: How to remove all rows if duplicate in SAS Programming

How to remove all rows if duplicate

Mchan890 — Mon, 09 Mar 2020 20:32:04 GMT

I have a large data set:

Have:

PO Amount Line
1212 300 1
1233 250 1
1233 600 2

1345 1520 1

1350 1000 2

1350 500 3

Want:

PO Amount Line
1212 300 1

1345 1520 1

I know the nodupkey will remove the duplicates, however, I am trying to remove both if there are duplicate.

Re: How to remove all rows if duplicate

novinosrin — Mon, 09 Mar 2020 20:37:41 GMT

Hi @Mchan890 Please try


data have;
input PO   Amount   Line;
cards;
1212        300     1         
1233        250     1        
1233        600     2        
1345      1520     1        
1350      1000     2
1350        500     3
;

data want;
 set have;
 by po;
 if first.po and last.po;
run;

/*or*/


proc sql;
create table want as
select *
from have
group by po
having count(*)=1;
quit;

Re: How to remove all rows if duplicate

Reeza — Mon, 09 Mar 2020 20:53:38 GMT

PROC SORT has a ~~UNIQUERECS~~ UNIQUEOUT option as well.

EDIT: corrected, as per @ballardw code - which is a correct answer.

Re: How to remove all rows if duplicate

novinosrin — Mon, 09 Mar 2020 20:45:24 GMT

Thank you @Reeza learned something new

Re: How to remove all rows if duplicate

ballardw — Mon, 09 Mar 2020 20:49:16 GMT

Since you want unique values of PO then:

data have;
   input PO   Amount   Line   ;
datalines;
1212        300     1         
1233        250     1        
1233        600     2        
1345      1520     1        
1350      1000     2
1350        500     3
;

Proc sort data=have out=have uniqueout=want nouniquekey;
   by po ;
run;

The data set you want is the one specified by the UNIQUEOUT option and the NOUNIQUEKEY is the appropriate equivalent to NODUPEKEY.

Please note how the data was provided as data step code so that we have something to test with.

Re: How to remove all rows if duplicate

novinosrin — Mon, 09 Mar 2020 20:53:48 GMT

Hmm Okay, so it's called UNIQUEOUT . I will have to remember that. Thank you!

Re: How to remove all rows if duplicate

Reeza — Mon, 09 Mar 2020 20:55:31 GMT

There used to be a UNIQUERECS option, it was removed because it was confusing and didn't work the way people expected and then they added UNIQUEOUT so I get them confused sometimes 🙂

Re: How to remove all rows if duplicate

novinosrin — Mon, 09 Mar 2020 20:58:41 GMT

Good point, I think it would help to keep abreast of which ones are active and not. I suppose there must be some docs, but really is confusing. Sir @ballardw is very good in knowing the latest stuff in the docs

Re: How to remove all rows if duplicate

Reeza — Mon, 09 Mar 2020 21:01:15 GMT

Unfortunately the key is checking it frequently....and I don't actually use SAS day to day anymore, which is why my participation is 'lower' and declining :(. We do everything at my current shop in R.

Re: How to remove all rows if duplicate

data_null__ — Mon, 09 Mar 2020 21:20:07 GMT

@ballardw wrote:

Since you want unique values of PO then:
data have;
   input PO   Amount   Line   ;
datalines;
1212        300     1         
1233        250     1        
1233        600     2        
1345      1520     1        
1350      1000     2
1350        500     3
;

Proc sort data=have out=have uniqueout=want nouniquekey;
   by po ;
run;
The data set you want is the one specified by the UNIQUEOUT option and the NOUNIQUEKEY is the appropriate equivalent to NODUPEKEY.

Please note how the data was provided as data step code so that we have something to test with.

Note: OUT= data will not have the same observations, as DATA= as might be mistakenly implied by your example: data=have out=have

Re: How to remove all rows if duplicate

ballardw — Mon, 09 Mar 2020 21:50:27 GMT

@Reeza wrote:
There used to be a UNIQUERECS option, it was removed because it was confusing and didn't work the way people expected and then they added UNIQUEOUT so I get them confused sometimes 🙂

I'm glad I wasn't the only one remembering another no longer available option. I wouldn't be surprised that code would run but without the documentation I wasn't about to try.

Re: How to remove all rows if duplicate

ballardw — Mon, 09 Mar 2020 21:52:44 GMT

@data_null__ wrote:
@ballardw wrote:

Since you want unique values of PO then:
data have;
   input PO   Amount   Line   ;
datalines;
1212        300     1         
1233        250     1        
1233        600     2        
1345      1520     1        
1350      1000     2
1350        500     3
;

Proc sort data=have out=have uniqueout=want nouniquekey;
   by po ;
run;
The data set you want is the one specified by the UNIQUEOUT option and the NOUNIQUEKEY is the appropriate equivalent to NODUPEKEY.

Please note how the data was provided as data step code so that we have something to test with.
Note: OUT= data will not have the same observations, as DATA= as might be mistakenly implied by your example: data=have out=have

I thought I was getting an error without the out=have, and probably should have named it Reduced or similar.

Re: How to remove all rows if duplicate

data_null__ — Mon, 09 Mar 2020 22:05:12 GMT

out=_null_

If you don't need the dups.

Re: How to remove all rows if duplicate

ChrisNZ — Tue, 10 Mar 2020 02:15:47 GMT

@Reeza Your lower is most people's unattainable!