topic Removing row from data set after comparing with previous rows value in SAS Enterprise Guide

Removing row from data set after comparing with previous rows value

deleted_user — Tue, 10 Feb 2009 06:42:49 GMT

For example:

Subscriber_Objid Account_ID Contact_Objid Contact_ID
1. 268435459 55 268435457 56
2. 268435459 NULL 268435457 NULL

The second row should be removed.

OR

Subscriber_Objid Account_ID Contact_Objid Contact_ID
1. 268435459 55 268435457 56
2. 268435459 56 268435457 NULL

The second row should NOT be removed. because Account_id is not same for both rows.

what i have to do ?

1. i have to compare two rows of data set having same Contact_Objid(not nullable column).
2. if both rows I and J having same Contact_Objid then i have to delete I th row from data set if all other columns of i th rows are having either same value of j th row or Null value.

please give me idea.....how can i implement .....i am thinking about lag function.

is it possible complete all process in one data step...?

thanks

Re: Removing row from data set after comparing with previous rows value

saroj — Tue, 10 Feb 2009 09:34:46 GMT

does the jth row follow the ith row or could the ith row and the jth row be any rows in the input file.

Re: Removing row from data set after comparing with previous rows value

deleted_user — Tue, 10 Feb 2009 10:23:35 GMT

thanks for giving time..
here j = i+1;

comparison between jth and i th row will take place . and one will be deleted. if i th row is subset of jth row then ith will be deleted. else vise versa.

Re: Removing row from data set after comparing with previous rows value

deleted_user — Tue, 10 Feb 2009 12:13:46 GMT

assuming you want just the last of equal groups of contact_objid and account_id, you could use by group processing (even if you need to add the notsorted option)
[pre]data new_data ;
do until( last.contact_objid ) ;
set original ;
by account_id notsorted contact_objid notsorted ;
end ;
run ;[/pre]However, this method depends on having actual Account_id rather than NULL.

good luck

PeterC

Re: Removing row from data set after comparing with previous rows value

saroj — Wed, 11 Feb 2009 04:38:08 GMT

how about using two input statements in the datastep to read two rows per iteration and output and not output based on comparison output ?