topic Re: Merging datasets but with specificity of duration of observations by dates in New SAS User

Merging datasets but with specificity of duration of observations by dates

femiajumobi1 — Tue, 18 Oct 2022 20:02:27 GMT

I wish to merge two datasets: Data_A and Data_B using ID. I am only interested in observations in B with dates variable FDS_B that are within 14 days of each of the dates in A (FDS_A). The date format is MM/DD/YYYY. How do I improve the code below if I were to include FDS_A and FDS_B?

Data want;
Merge Data_A Data_B;
by ID;
run;

Re: Merging datasets but with specificity of duration of observations by dates

ballardw — Tue, 18 Oct 2022 21:02:07 GMT

Maybe something along the lines of

Proc sql;
   create table want a
   select a,*, b.fds_b
   from data_a as a
           left join
           data_b as b
           on a.id=b.id
   where abs(a.fds_a-b.fds_b) le 14
  ;
quit;

Maybe. If your Fds variables are actual SAS date variables.

You do not mention any other variables from Data_b. You would have to explicitly list them using the b.variable syntax. If the variable is the same name as one in Data_A do you expect it to be a different variable or replace the value of the variable in the Data_a.

I am a bit concerned about exactly what you mean by "each of the dates in A". That could be taken different ways.

If the above suggestion doesn't come close then show some example data from each of the two sets and what you expect for the result.

Re: Merging datasets but with specificity of duration of observations by dates

mkeintz — Wed, 19 Oct 2022 02:35:14 GMT

What do you want your output dataset to look like?
Will it have any unmatched obs from a or b?
What if multiple b's match a single a, or vice versa?
Or worse yet, what if multiple b's match multiple a's?

Some of these questions will be simplified if we knew (for instance) that either a or b was limited to one obs per id. Is that the case/

If you provided some sample data in the form of a working data step, I think you will get some suggested code to address the request.

Re: Merging datasets but with specificity of duration of observations by dates

tarheel13 — Wed, 19 Oct 2022 11:58:10 GMT

proc sql with inexact matching.

Re: Merging datasets but with specificity of duration of observations by dates

femiajumobi1 — Sat, 22 Oct 2022 12:52:43 GMT

I really appreciate your prompt response. Thanks. I had a detour on the approach and will get back for further clarifications.

Re: Merging datasets but with specificity of duration of observations by dates

femiajumobi1 — Sat, 22 Oct 2022 12:53:34 GMT

I really appreciate your prompt response. Thanks. I had a detour on the approach and will get back for further clarifications.