Desktop productivity for business analysts and programmers

Removing row from data set after comparing with previous rows value

Reply
Not applicable
Posts: 0

Removing row from data set after comparing with previous rows value

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
Occasional Contributor
Posts: 16

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

Posted in reply to deleted_user
does the jth row follow the ith row or could the ith row and the jth row be any rows in the input file.
Not applicable
Posts: 0

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

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.
Not applicable
Posts: 0

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

Posted in reply to deleted_user
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
Occasional Contributor
Posts: 16

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

Posted in reply to deleted_user
how about using two input statements in the datastep to read two rows per iteration and output and not output based on comparison output ?
Ask a Question
Discussion stats
  • 4 replies
  • 174 views
  • 0 likes
  • 2 in conversation