topic Re: Need help with merge in SAS Programming

Need help with merge

kpdoe — Fri, 21 Apr 2023 03:26:22 GMT

data A;
  input ACCT Date mmddyy10.;
  format Date mmddyy10.;
  datalines;
1111 10/31/2022
1111 02/26/2023
;
run;

/* Creating Dataset B */
data B;
  input ACCT Txt $ Src_Date mmddyy10.;
  format Src_Date mmddyy10.;
  datalines;
1111 ABC 03/23/2023
;
run;

ACCT	Date	Txt
1111	10/31/2022
1111	02/28/2023	ABC

Is this result achievable without having a matching date filed? Thanks in advance for any help!

Re: Need help with merge

ballardw — Fri, 21 Apr 2023 04:44:55 GMT

Why do you have a date in the example of what you want of 2/28/2023? None of the data you show has that in the source.

You probably should provide larger example data sets, along with the rule(s) involved in matching. I suspect I can write something that would work for case of exactly two records and exactly one record in the other set that would not extend at all.

If, as I suspect, the definition is going to involve some sort of "closest" date rule then you need to be very careful in defining how multiple dates that might be the same number of days apart are chosen. Consider a date of 15 Mar 2023 in one set and then other with same ACCT but dates of 13 Mar 2023 and 17 Mar 2023. Both are two days from 15 Mar. So which gets chosen and why.

@kpdoe wrote:
data A;
  input ACCT Date mmddyy10.;
  format Date mmddyy10.;
  datalines;
1111 10/31/2022
1111 02/26/2023
;
run;

/* Creating Dataset B */
data B;
  input ACCT Txt $ Src_Date mmddyy10.;
  format Src_Date mmddyy10.;
  datalines;
1111 ABC 03/23/2023
;
run;
ACCT Date Txt

1111 10/31/2022

1111 02/28/2023 ABC

Is this result achievable without having a matching date filed? Thanks in advance for any help!

Re: Need help with merge

vijaypratap0195 — Fri, 21 Apr 2023 05:41:26 GMT

Your question is not clear. Can you also provide the criteria to join the 2 datasets.

It will be more clear if you can also provide an example over a larger dataset.

-Vijay

Re: Need help with merge

Ksharp — Fri, 21 Apr 2023 11:50:12 GMT

/*
I think you should post more data or example to
illustrate what you are looking for.
*/

data want;
 merge A B(keep=ACCT txt rename=(txt=_txt));
 by ACCT;
 if last.ACCT then txt=_txt;
 drop _txt;
run;