topic Removing both observations in a duplicate in SAS Procedures

Removing both observations in a duplicate

akberali67 — Mon, 07 Nov 2011 16:16:52 GMT

Hi,

I want to remove two instances of a complete duplicate observation. I mean using nodup I can remove one observation, how can I remove both of them, can I write a condition?

Example of data:

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

WXYZ 2323 1 3212 672 020 6,543.89 2011-02-28 Allowance

I want to remove both of the first two lines which duplicate each other. I am just not able to figure out the logic. I have 9 variables in the data. What I am trying to do is remove equal Amount of debit and a credit of a transaction. So it should not remove all the duplicates but any two perfect duplicates throughout the data.Please help.

Thanks in advance.:smileyplain:

--Akber.

Removing both observations in a duplicate

FriedEgg — Mon, 07 Nov 2011 16:22:55 GMT

There is probably a more efficient way to do this but here is one way... Use proc sort with dupout option and nodupkey, merge the dupout file back to the original data to remove the blanks.

data foo;

*this contains my data;

run;

proc sort data=foo out=bar dupout=drops nodupkey; by id trans_id; run;

data bar;

merge drops(in=b) bar(in=a);

by id trans_id;

if a and not b;

run;

Re: Removing both observations in a duplicate

art297 — Mon, 07 Nov 2011 16:27:45 GMT

A more efficient way, I think, would be to remove the duplicates in a datastep. e.g.:

options datestyle=ymd;

data have;

informat var1 $10.;

informat var2-var6 12.;

informat var7 comma12.;

informat var8 anydtdte10.;

informat var9 $15.;

input var1-var8 var9 &;

cards;

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

WXYZ 2323 1 3212 672 020 6,543.89 2011-02-28 Allowance

;

proc sort data=have;

by var1-var9;

run;

data want;

set have;

by var1-var9;

if first.var9 and last.var9;

run;

Re: Removing both observations in a duplicate

MikeZdeb — Tue, 08 Nov 2011 02:00:35 GMT

Hi ... another idea using Art's data ...

proc sql;

create table want as

select *

from have

group by var1,var2,var3,var4,var5,var6,var7,var8,var9

having count(*) eq 1;

quit;

Removing both observations in a duplicate

Ksharp — Tue, 08 Nov 2011 03:19:03 GMT

Another way is SQL. If you like:

options datestyle ymd;
data have;
  informat var1 $10.;
  informat var2-var6 12.;
  informat var7 comma12.;
  informat var8 anydtdte10.;
  informat var9 $15.;
  input var1-var8 var9 &;
  cards;
ABCD  1234  1  1202  671  020       1,234.56   2011-01-31  Fees Paid
ABCD  1234  1  1202  671  020       1,234.56   2011-01-31  Fees Paid
WXYZ  2323  1  3212  672  020       6,543.89   2011-02-28  Allowance
;
run;
proc sql;
 create table want as
  select * 
   from have
    group by var1,var2,var3,var4,var5,var6,var7,var8,var9
     having count(*) eq 1;
quit;

Ksharp

Re: Removing both observations in a duplicate

art297 — Tue, 08 Nov 2011 04:07:20 GMT

If we're going to go a proc sql route I'd use:

proc sql noprint;

select name into :vars separated by ","

from dictionary.columns

where libname eq "WORK"

and memname eq "HAVE"

;

create table want as

select *

from have

group by &vars.

having count(*) eq 1

;

quit;

Re: Removing both observations in a duplicate

Ksharp — Tue, 08 Nov 2011 08:23:18 GMT

En. It is a better way to save typing.

Re: Removing both observations in a duplicate

MikeZdeb — Tue, 08 Nov 2011 14:22:41 GMT

hi ... depends, in this case ...

94 characters

select name into :vars separated by ","

from dictionary.columns

where libname eq "WORK"

and memname eq "HAVE"

;

(plus &vars = 99)

44 characters

var1,var2,var3,var4,var5,var6,var7,var8,var9

ps the "when to hold 'em versus when to fold 'em" rule

Re: Removing both observations in a duplicate

art297 — Tue, 08 Nov 2011 14:28:22 GMT

and/or the "when to make use of the SAS Macro/Abbreviations rule that advises to assign a hotkey to quickly insert frequently used code snippets"

Re: Removing both observations in a duplicate

akberali67 — Tue, 08 Nov 2011 15:49:49 GMT

Hi,

How can I simply remove TWO observations with equal Amount with a different sign, based just on amount and no other field?? Any two observations, no more no less.

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

ABCD 1234 1 1202 671 020 -1,234.56 2011-01-31 Fees Paid

ABCD 2345 1 1203 672 030 1,234.56 2011-01-31 Fees Paid

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

WXYZ 2323 1 3212 672 020 6,543.89 2011-02-28 Allowance

Like line 1 & 2 or line 2 & 3 or line 2 & 4 but not more than 2. I mean any two random lines which

Thanks,

Akber.

Re: Removing both observations in a duplicate

art297 — Tue, 08 Nov 2011 16:31:08 GMT

I'm not sure exactly what you are trying to do. You can always use the abs() function to make all values positive in your comparisons.

Re: Removing both observations in a duplicate

OS2Rules — Tue, 08 Nov 2011 16:54:33 GMT

Art:

If you are going with the Proc SQL then you may be limited buy the size of th :into var.

A large dataset could blow the variable size limit whereas the first. / last. on the sort won't.

Just sayin'

Re: Removing both observations in a duplicate

art297 — Tue, 08 Nov 2011 17:03:03 GMT

My original suggestion didn't use either proc sql or macro variables. However, you are definitely correct, although that limit is now 65,534. If one has a collection of variable names that exceed that number of characters, I definitely wouldn't suggest a method that requires manually entering them.

Removing both observations in a duplicate

FriedEgg — Tue, 08 Nov 2011 17:24:33 GMT

data have;

input slug :$10. (gar h3432 fing up99 thisaw) (:12.) _dllr :comma9.2 dte :yymmdd10. note &:$15. ;

_dllr=abs(_dllr);

cards;

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

ABCD 1234 1 1202 671 020 -1,234.56 2011-01-31 Fees Paid

ABCD 2345 1 1203 672 030 1,234.56 2011-01-31 Fees Paid

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

WXYZ 2323 1 3212 672 020 6,543.89 2011-02-28 Allowance

;

run;

proc sort data=have; by _all_; run;

data _null_;

set sashelp.vcolumn(where=(libname='WORK' and memname='HAVE')) end=done;

if done then call symputx('nvar',name);

run;

data want;

set have;

by _all_;

if first.&nvar and last.&nvar then output;

run;

Removing both observations in a duplicate

akberali67 — Wed, 09 Nov 2011 10:45:13 GMT

yes but that would then remove any two transactions. I only want a positive and negative with the same value removed

say 1234 and 2345 is the reference# then how do I remove the first and second observation (both 1234), both of which will add to net out to zero, how can I delete them from my dataset

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

ABCD 1234 2 1203 671 020 -1,234.56 2011-01-31 Fees Paid

ABCD 2345 1 1203 672 030 1,234.56 2011-01-31 Fees Paid

Removing both observations in a duplicate

art297 — Wed, 09 Nov 2011 13:17:46 GMT

You sort (order) the file, say with proc sql, by reference # and the abs() of the variable you want to use. Then, you can use the look ahead-look back methodology to see if any adjacent values add to zero (see: http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back )

Re: Removing both observations in a duplicate

FriedEgg — Wed, 09 Nov 2011 19:06:04 GMT

Okay, I think I understand your requirements now.

data have;

input slug :$10. (gar h3432 fing up99 thisaw) (:12.) _dllr :comma9.2 dte :yymmdd10. note &:$15. ;

cards;

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

ABCD 1234 1 1202 671 020 -1,234.56 2011-01-31 Fees Paid

ABCD 2345 1 1203 672 030 1,234.56 2011-01-31 Fees Paid

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

WXYZ 2323 1 3212 672 020 6,543.89 2011-02-28 Allowance

ERDS 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

ERDS 1234 1 1202 671 020 -1,234.56 2011-01-31 Fees Paid

QRDS 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid

;

run;

proc sort data=have; by _all_; run;

data check;

if 0 then __dllr=0;

declare hash ha(dataset:'have(rename=(_dllr=__dllr) where=(__dllr>0))');

ha.definekey('slug','gar','h3432','fing','up99','thisaw');

ha.definedata(all:'y');

ha.definedone();

do until(done);

set have end=done;

by slug gar h3432 fing up99 thisaw _dllr dte note;

if first.thisaw and ha.find()=0 and sum(of _dllr __dllr)>0 then output;

if last.thisaw and last.note and not(first.note) then output;

end;

stop;

drop __:;

run;

proc print data=check noobs; run;

INPUT DATA 1

slug gar h3432 fing up99 thisaw _dllr dte note

ABCD 1234 1 1202 671 20 -1234.56 18658 Fees Paid

ABCD 1234 1 1202 671 20 1234.56 18658 Fees Paid

ABCD 2345 1 1203 672 30 1234.56 18658 Fees Paid

ERDS 1234 1 1202 671 20 -1234.56 18658 Fees Paid

ERDS 1234 1 1202 671 20 1234.56 18658 Fees Paid

QRDS 1234 1 1202 671 20 1234.56 18658 Fees Paid

WXYZ 2323 1 3212 672 20 6543.89 18686 Allowance

OUTPUT DATA 2

slug gar h3432 fing up99 thisaw _dllr dte note

ABCD 1234 1 1202 671 20 1234.56 18658 Fees Paid

ABCD 2345 1 1203 672 30 1234.56 18658 Fees Paid

QRDS 1234 1 1202 671 20 1234.56 18658 Fees Paid

WXYZ 2323 1 3212 672 20 6543.89 18686 Allowance

I want to remove:

Obs1 and 2 because they cancel eachother out (buy and return) but not 3 (repurchase)

Obs4 is a standalone item purchase, it stays

Obs5 and 6 are removed (buy and return)

Obs6 and 7 stay (two identical purchases, not purchase and return)

Obs8 stays, standalone purchase.

Re: Removing both observations in a duplicate

akberali67 — Wed, 09 Nov 2011 20:15:14 GMT

Ok, that seems a lil complicated.

I edited the program to fit the data but I am getting a lot of errors. I made a new field called JE_ID to fix problems with another fields which is additional here

Program:

PROC SORT DATA = SASDATA.Q1Q2_SALES;

BY _ALL_;

RUN;

DATA SASDATA.Z_Q1Q2_SALES;

IF 0 THEN MONETARY_AMOUNT=0;

DECLARE HASH HA(DATASET:'SASDATA.Q1Q2_SALES WHERE=(MONETARY_AMOUNT>0))');

HA.DEFINEKEY('OPERATOR','JOURNAL','LINE__','UNIT','ACCT','DEPARTMENT','DATE','JOURNAL_LINE_DESCRIPTION','JE_ID');

HA.DEFINEDATA(ALL:'Y');

HA.DEFINEDONE();

DO UNTIL(DONE);

SET SASDATA.Q1Q2_SALES END=DONE;

BY OPERATOR JOURNAL LINE__ UNIT ACCT DEPARTMENT MONETARY_AMOUNT DATE JOURNAL_LINE_DESCRIPTION JE_ID;

IF FIRST.DEPARTMENT AND HA.FIND()=0 AND SUM(OF MONETARY_AMOUNT)>0 THEN OUTPUT;

IF LAST.DEPARTMENT AND LAST.JOURNAL_LINE_DESCRIPTION AND NOT(FIRST.JOURNAL_LINE_DESCRIPTION) THEN OUTPUT;

END;

STOP;

RUN;

Log:

304

305 PROC SORT DATA = SASDATA.Q1Q2_SALES;

306 BY _ALL_;

307 RUN;

NOTE: There were 206 observations read from the data set SASDATA.Q1Q2_SALES.

NOTE: The data set SASDATA.Q1Q2_SALES has 206 observations and 11 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

308

309 DATA SASDATA.Z_Q1Q2_SALES;

310 IF 0 THEN MONETARY_AMOUNT=0;

311 DECLARE HASH HA(DATASET:'SASDATA.Q1Q2_SALES WHERE=(MONETARY_AMOUNT>0))');

312 HA.DEFINEKEY('OPERATOR','JOURNAL','LINE__','UNIT','ACCT','DEPARTMENT','DATE','JOURNAL_LINE_DESCRI

312! PTION','JE_ID');

313 HA.DEFINEDATA(ALL:'Y');

314 HA.DEFINEDONE();

315

316

317 DO UNTIL(DONE);

318 SET SASDATA.Q1Q2_SALES END=DONE;

319 BY OPERATOR JOURNAL LINE__ UNIT ACCT DEPARTMENT MONETARY_AMOUNT DATE JOURNAL_LINE_DESCRIPTION

319! JE_ID;

320 IF FIRST.DEPARTMENT AND HA.FIND()=0 AND SUM(OF MONETARY_AMOUNT)>0 THEN OUTPUT;

321 IF LAST.DEPARTMENT AND LAST.JOURNAL_LINE_DESCRIPTION AND NOT(FIRST.JOURNAL_LINE_DESCRIPTION) THEN

321! OUTPUT;

322 END;

323 STOP;

324 RUN;

ERROR: Invalid data set name at line 313 column 1.

ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 1 observations read from the data set SASDATA.Q1Q2_SALES.

WARNING: The data set SASDATA.Z_Q1Q2_SALES may be incomplete. When this step was stopped there were

0 observations and 11 variables.

WARNING: Data set SASDATA.Z_Q1Q2_SALES was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

Cant figure whats going wrong, please help

Re: Removing both observations in a duplicate

art297 — Wed, 09 Nov 2011 22:17:47 GMT

Akber, I think that you've confused matters by changing the variable names and now having one more variable than you showed in your example data.

Regardless, I still think that the methodology I proposed the other day will still be the easiest for you to manage (although it might not be the most efficient code). I may have some of the variables or ordering wrong, but doesn't the following do what you want?:

PROC SORT DATA = Q1Q2_SALES;

BY OPERATOR JOURNAL LINE__ UNIT ACCT DEPARTMENT

DATE MONETARY_AMOUNT

JOURNAL_LINE_DESCRIPTION;

RUN;

data Z_Q1Q2_SALES;

retain delete_me;

if _n_ eq 1 then delete_me=0;

set Q1Q2_SALES;

by OPERATOR JOURNAL LINE__ UNIT ACCT DEPARTMENT

DATE JOURNAL_LINE_DESCRIPTION;

set Q1Q2_SALES ( firstobs = 2 keep = MONETARY_AMOUNT

rename = (MONETARY_AMOUNT = Next_MONETARY_AMOUNT) )

Q1Q2_SALES ( obs = 1 drop = _all_ );

if delete_me then do;

output;

delete_me=0;

end;

else if not last.journal_line_description then do;

if sum(monetary_amount,next_monetary_amount) eq 0 then do;

delete_me=1;

output;

end;

else output;

end;

else output;

run;

data Z_Q1Q2_SALES;

set Z_Q1Q2_SALES;

if delete_me eq 0;

run;

Removing both observations in a duplicate

FriedEgg — Wed, 09 Nov 2011 22:59:50 GMT

It looks like you left out a number of details when transcribing my code.

data have;

input operator :$10. (journal line__ unit acct department) (:12.) monetary_amount :comma9.2 date :yymmdd10. journal_line_description &:$15. je_id;

cards;

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid 1

ABCD 1234 1 1202 671 020 -1,234.56 2011-01-31 Fees Paid 1

ABCD 2345 1 1203 672 030 1,234.56 2011-01-31 Fees Paid 1

ABCD 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid 1

WXYZ 2323 1 3212 672 020 6,543.89 2011-02-28 Allowance 1

ERDS 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid 1

ERDS 1234 1 1202 671 020 -1,234.56 2011-01-31 Fees Paid 1

QRDS 1234 1 1202 671 020 1,234.56 2011-01-31 Fees Paid 1

;

run;

proc sort data=have; by operator journal line__ unit acct department monetary_amount date journal_line_description; run;

data check;

if 0 then __dllr=0;

declare hash ha(dataset:'have(rename=(monetary_amount=__dllr) where=(__dllr>0))');

ha.definekey('journal','line__','unit','acct','department');

ha.definedata('__dllr');

ha.definedone();

do until(done);

set have end=done;

by operator journal line__ unit acct department monetary_amount date journal_line_description;

if first.department and ha.find()=0 and sum(of monetary_amount __dllr)>0 then output;

if last.department and last.journal_line_description and not(first.journal_line_description) then output;

end;

stop;

drop __:;

run;

options nodate;

proc print data=have noobs; title 'INPUT DATA'; run;

proc print data=check noobs; title 'OUTPUT DATA'; run;

monetary_ journal_line_

operator journal line__ unit acct department amount date description je_id

ABCD 1234 1 1202 671 20 -1234.56 18658 Fees Paid 1

ABCD 1234 1 1202 671 20 1234.56 18658 Fees Paid 1

ABCD 2345 1 1203 672 30 1234.56 18658 Fees Paid 1

ERDS 1234 1 1202 671 20 -1234.56 18658 Fees Paid 1

ERDS 1234 1 1202 671 20 1234.56 18658 Fees Paid 1

QRDS 1234 1 1202 671 20 1234.56 18658 Fees Paid 1

WXYZ 2323 1 3212 672 20 6543.89 18686 Allowance 1

monetary_ journal_line_

operator journal line__ unit acct department amount date description je_id

ABCD 1234 1 1202 671 20 1234.56 18658 Fees Paid 1

ABCD 2345 1 1203 672 30 1234.56 18658 Fees Paid 1

QRDS 1234 1 1202 671 20 1234.56 18658 Fees Paid 1

WXYZ 2323 1 3212 672 20 6543.89 18686 Allowance 1