topic Re: While Joining two tables output of column should be exact match and missing should be previous R in SAS Programming

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

sivastat08 — Fri, 24 May 2019 17:56:03 GMT

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

Re: While Joining two tables output of column should be exact match and missing should be previous R

ballardw — Fri, 24 May 2019 17:58:16 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: While Joining two tables output of column should be exact match and missing should be previous R

Kurt_Bremser — Sat, 25 May 2019 12:24:59 GMT

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.

Re: While Joining two tables output of column should be exact match and missing should be previous R

PGStats — Sat, 25 May 2019 21:58:43 GMT

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;

Re: While Joining two tables output of column should be exact match and missing should be previous R

sivastat08 — Tue, 28 May 2019 09:43:09 GMT

Sure Sir.Understood Thanks!

Re: While Joining two tables output of column should be exact match and missing should be previous R

sivastat08 — Tue, 28 May 2019 09:49:58 GMT

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;

Re: While Joining two tables output of column should be exact match and missing should be previous R

sivastat08 — Tue, 28 May 2019 09:51:36 GMT

Thanks!