PROC SQL

[DLROW]

Hi,

Is there a way to compare one observation with other observation. Here, I need to compare Currentcontact(Row1 : Value: 123456789) with previous contact(Row 2 : Value : 123456789) If both are same then current contact (row2 : Value 143265782) should populate.? Thanks.

data contactnumbers;
input currentcontact previouscontact;
datalines;
123456789 123456879
123465782 123456789
;

currentcontact           previouscontact
123456789               123456231
143265782               123456789

[PeterClemmensen]

I'm not sure if this is what you want, but perhaps use the lag function?

data contactnumbers;
input currentcontact previouscontact;
datalines;
123456789 123456879
123465782 123456789
;

data test;
   set contactnumbers;
   lagcurrent=lag1(currentcontact);

   if (lagcurrent=previouscontact) then do;
      /* Whatever you want to do */
   end;
run;

[DLROW]

Sorry, I wasn't clear. Those two rows are in different tables. Lets say row 1 is in table_1 and row 2 is in table_2. I already  tried data step with lag function but I am not sure how to code it if the rows to be compared are on the different tables. Thanks.

Table_1 :

currentcontact           previouscontact
123456789               123456231

Table_2:

currentcontact           previouscontact
143265782               123456789

[ballardw]

Example data and expected results will likely save a lot of time. => 3 data sets, the two input and the results.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[DLROW]

Please see below. Thanks.

data contactnumbers1;
input currentcontact previouscontact id;
datalines;
1111111111 4356784532 12
1111111111 4356784532 86
;

data contactnumbers2;
input currentcontact previouscontact id;
datalines;
234567865 1234565408 54
222222222 1111111111 86

;

If Previous contact in contactnumbers2 (1111111111 (ID:86) ) is equal to currentcontact of Contactnumbers1 (1111111111 (ID:86)) then the output shoud be like the one below..

Output:

CurrentContact	PreviousContact	ID
1111111111	4356784532	12
1111111111	4356784532	86
222222222	1111111111	86

[novinosrin]

data contactnumbers1;
input currentcontact $ previouscontact $ id;
datalines;
1111111111 4356784532 12
1111111111 4356784532 86
;

data contactnumbers2;
input currentcontact $ previouscontact$ id;
datalines;
234567865 1234565408 54
222222222 1111111111 86
;

proc sql;
create table want as
select * 
from contactnumbers1
union
select *
from contactnumbers2
where previouscontact in (select currentcontact from contactnumbers1);
quit;

[ballardw]

Will every record in contactnumbers1 have at least one match with your rule in contactnumbers2? If not, you need to provide an example what happens in that case (is the record kept or dropped and what order).

Does the value of ID play any actual role in this process? If so, what might it be?

What should the result look like if two or more records in contactnumbers2 have the same previouscontact but different currentcontacts?

Is there any explicit or implied sort order in the result?