While Joining two tables output of column should be exact match and missing should be previous Recor

[sivastat08]

Hi ,

While Left Joining two data sets ,output of a data set should contains only exactly matched and whatever the observations not matched it should pick previous record(Date).Could you please help me on getting this.

Attached excel files

[ballardw]

And the output should look like what for that input?

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

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.

[sivastat08]

Thanks!

[Kurt_Bremser]

If you need help with Excel, you should consult a Microsoft-oriented forum.

If you need help with SAS datasets, please provide such in a usable way (data steps with datelines), so we have a clear picture of what you are working with.

[sivastat08]

Sure Sir.Understood Thanks!

[PGStats · Accepted solution]

Try this:

proc sql;
select left.transId, workdate, snapDate
from
left left join right on left.transId=right.transId and workDate >= snapDate
group by left.transId, left.workDate
having snapDate = max(snapDate);
quit;

[sivastat08]

Thanks for your support sir.

The code is working fine.

Here is my code

proc sort data= Test_A;by transId workDate;run;
proc sort data= Test_B;by transId snapDate;run;

proc sql;

create table New as
select lft.transId, lft.workdate, Rgt.snapDate
from work.Test_A lft
left join TEST_B Rgt on lft.transId= Rgt.transId and lft.workDate >= Rgt.snapDate
group by lft.transId, lft.workDate
having Rgt.snapDate = max(Rgt.snapDate);
quit;