Desktop productivity for business analysts and programmers

Removing row from data set after comparing with previous rows value

Reply
N/A
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

does the jth row follow the ith row or could the ith row and the jth row be any rows in the input file.
N/A
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.
N/A
Posts: 0

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

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

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
  • 147 views
  • 0 likes
  • 2 in conversation