need to display same variable values on the table

[cho16]

 Hello team,

We have two tables 1 and 2. The PRV is the primary key.

We should display only the equal LOB values from the two tables. For example , in table 1  the PRV 1 has the LOB value EPA and table 2 has two different values EPA and HPL. But it should display as EPA  in table 2.

Table 1 :

PRV	LOB	EXP	porg
1	EPA	99999	1
1	EPA	99999	2
2	EPA	99999	3
2	EPC	99999	4
3	EPA	99999	5
3	HPL	99999	6

Table 2:

PRV	LOB	EXP	Parent
1	EPA	99999	G
1	HPL	99999	H
2	EPA	99999	J
2	EPC	99999	I
3	EPA	99999	O
3	HPL	99999	O

Results:

PRV	LOB	EXP	Parent
1	EPA	99999	G
2	EPA	99999	J
2	EPC	99999	I
3	EPA	99999	O
3	HPL	99999	O

Thanks in advance.

[PaigeMiller]

---

@cho16 wrote:

We have two tables 1 and 2. The PRV is the primary key.

We should display only the equal LOB values from the two tables. For example , in table 1  the PRV 1 has the LOB value EPA and table 2 has two different values EPA and HPL. But it should display as EPA  in table 2.

---

Why? What is the logic here? In order for us to write code, you need to explain the logic and general rules we can follow to create a program that works in all cases.

[cho16]

I have a requirement to compare the two datasets with different variables. There is a primary key called PRV. If there are records that has two different values on the variable LOB for the one PRV, then it should display with the same record  on the table 2 as shown in the above example.

[r_behata · Accepted solution]

Proc SQL;
	Create table want as 
		Select b.*
		from table1 a,
			table2 b
		where a.prv=b.prv
		and a.lob=b.lob;
Quit;