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.
Proc SQL;
Create table want as
Select b.*
from table1 a,
table2 b
where a.prv=b.prv
and a.lob=b.lob;
Quit;
@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.
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.
Proc SQL;
Create table want as
Select b.*
from table1 a,
table2 b
where a.prv=b.prv
and a.lob=b.lob;
Quit;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.