Below is the data i have an am trying to merge. I have also included the expected output but my code is only merging on the first match.

I have Data in Beta that is updated the following week. For instance for 9/6 week (it updates on 9/14 per say). This data changes week to week and as it updates Alpha table starts pulling in the next week. Beta only contains a monday date so i have dateparted it to get the week date to match this with every day of that week which is stored in beta. So since 9/6wk is 22592 i want to match with every day of that week stored in alpha so that would be 9/6-9/10. So i have date parted those dates as well so it displays which week they are thus beta should match with dates 9/6-9/10 in this example.

Note i do not expect or what to match with future weeks but i still need to keep the content. So i just want to match the previous week data in Beta with the previous weeks data in Alpha.

Beta table: only houses previous weeks data alpha table: houses previous week current week data.

I want to take the data in B

Table Alpha- This table stores the main info including the dates of each week mon-friday. Note this monday was not included due to holiday.

Table Beta- this table stores 2 extra columns of data relevant to table alpha. However it contains no dates but the Monday date for the previous week and info relevant to that.

Data want

My attempt:

DATA Alpha; drop monday;
Merge Alpha (in=a) Beta (in=b);
by id pkg week;
if a;
run;

i tried this because i need to keep everything in table A but want to add the info in table B. However its only matching on the first one.

CodePudding user response：

Your code works fine. But since you include the WEEK variable as a key for the merge the second ID/PKG combination from ALPHA will not match any observations from BETA since the week value in ALPHA is one week after the week value in BETA.

With your sample data you only need to use ID and PKG to merge ALPHA and BETA.

data alpha; 
  input ID $ PKG $ WeekA :yymmdd. IS $ SE $ DATE :yymmdd.;
  format weekA date yymmdd10.;
cards;
70 17AB 2021-09-06 Y N 2021-09-07
70 17AB 2021-09-06 Y N 2021-09-08
70 17AB 2021-09-06 Y N 2021-09-09
70 17AB 2021-09-06 Y N 2021-09-10
70 17AX 2021-09-13 - N 2021-09-13
70 17AX 2021-09-13 - N 2021-09-14
70 17AX 2021-09-13 - N 2021-09-15
;

data beta;
  input ID $ PKG $ WeekB :yymmdd. Add1 Add2 Monday :yymmdd.;
  format weekB monday yymmdd10.;
cards;
70 17AB 2021-09-06 Y N 2021-09-06
70 17AX 2021-09-06 Y N 2021-09-06
;

data want;
  merge alpha beta;
  by id pkg ;
run;

Results:

ID PKG       WeekA IS SE       DATE       WeekB Add1 Add2     Monday

70 17AB 2021-09-06 Y  N  2021-09-07  2021-09-06   Y    N  2021-09-06
70 17AB 2021-09-06 Y  N  2021-09-08  2021-09-06   Y    N  2021-09-06
70 17AB 2021-09-06 Y  N  2021-09-09  2021-09-06   Y    N  2021-09-06
70 17AB 2021-09-06 Y  N  2021-09-10  2021-09-06   Y    N  2021-09-06
70 17AX 2021-09-13 -  N  2021-09-13  2021-09-06   Y    N  2021-09-06
70 17AX 2021-09-13 -  N  2021-09-14  2021-09-06   Y    N  2021-09-06
70 17AX 2021-09-13 -  N  2021-09-15  2021-09-06   Y    N  2021-09-06

If the goal is to find the values of ADD1 and ADD2 from the most recent DATE in BETA then perhaps you really want to interleave the observations by date instead of merging. Then even if the DATE in BETA is a week before the DATE in ALPHA the values of ADD1 and ADD2 will be copied onto the observations from ALPHA.

Make new variables to retain the values of ADD1 and ADD2 from the observations read from BETA onto the following observations for ALPHA.

data alpha; 
  input ID $ PKG $ IS $ SE $ DATE :yymmdd.;
  format date yymmdd10.;
cards;
70 17AB Y N 2021-09-07
70 17AB Y N 2021-09-08
70 17AB Y N 2021-09-09
70 17AB Y N 2021-09-10
70 17AX - N 2021-09-13
70 17AX - N 2021-09-14
70 17AX - N 2021-09-15
;

data beta;
  input ID $ PKG $ DATE :yymmdd. Add1 Add2 ;
  format DATE yymmdd10.;
cards;
70 17AB 2021-09-06 Y N
70 17AX 2021-09-06 Y N
;

data want;
  set beta(in=inb) alpha(in=ina);
  by id pkg date;
  if inb then do; 
    retain _add1 _add2;
    _add1=add1; 
    _add2=add2;
    rename _add1=add1 _add2=add2;
    drop add1 add2;
  end;
  if ina then output;
  if last.pkg then call missing(_add1,_add2);
run;

Results

ID    PKG           DATE    IS    SE    add1    add2

70    17AB    2021-09-07    Y     N       Y       N
70    17AB    2021-09-08    Y     N       Y       N
70    17AB    2021-09-09    Y     N       Y       N
70    17AB    2021-09-10    Y     N       Y       N
70    17AX    2021-09-13    -     N       Y       N
70    17AX    2021-09-14    -     N       Y       N
70    17AX    2021-09-15    -     N       Y       N

CodePudding user response：

So as Tom mentioned what I had does work. For some reason it was bugging out when I was running it yesterday. I think the issue was in my code I had my by variables in the wrong order when I typed them wrong on here. A silly mistake but none the less thank you Tom for the help!

DATA Alpha; drop monday;
Merge Alpha (in=a) Beta (in=b);
by id pkg week;
if a;

run;