Hi Tom, Thank you. Finally, it works. It seems the tab character for indention is causing the issue. There are now observations found.  100 %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST); MPRINT(DYNAMIC_LEFT_JOIN): data UNQ_KEY; MPRINT(DYNAMIC_LEFT_JOIN): set CTL_JOINFLD; MPRINT(DYNAMIC_LEFT_JOIN): length t1 t2 $ 30; MPRINT(DYNAMIC_LEFT_JOIN): t1 = trim(tbl_name1); MPRINT(DYNAMIC_LEFT_JOIN): t2 = trim(tbl_name2); MPRINT(DYNAMIC_LEFT_JOIN): put t1=t2=; MPRINT(DYNAMIC_LEFT_JOIN): if t1 eq "COUNTERPARTY" and t2 eq "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); MPRINT(DYNAMIC_LEFT_JOIN): else delete; MPRINT(DYNAMIC_LEFT_JOIN): run; t1=COUNTERPARTY t2=COUNTERPARTY_HIST t1=COUNTERPARTY t2=COUNTERPARTY_HIST t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD. NOTE: The data set WORK.UNQ_KEY has 2 observations and 7 variables. ... View more

Hi @ballardw ,   For the LOSTCARDS warning, I just mistakenly entered a new line after the 4th record in my datalines step. Adter removing the new line, no more lostcard warning found.   For the tbl_name1 and tbl_name2, I found out that there are trailing spaces after Counterparty and Counterpart_hist. You can check the logs that I inserted.   1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 data CTL_JOINFLD; 70 infile datalines dsd /*delimiter=','*/; 71 length tbl_name1 $25. tbl_name2 $25. id $20. prim_key $20.; 72 input tbl_name1 $ tbl_name2 $ id $ prim_key $; 73 datalines; NOTE: The data set WORK.CTL_JOINFLD has 4 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 667.43k OS Memory 19876.00k Timestamp 08/16/2024 04:07:39 PM Step Count 31 Switch Count 2 Page Faults 0 Page Reclaims 125 Page Swaps 0 Voluntary Context Switches 10 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 78 ; 79 run; 80 81 %macro dynamic_left_join(dataset1,dataset2); 82 83 84 data UNQ_KEY; 85 set CTL_JOINFLD; /*Used the table created above*/ 86 length t1 t2 $ 100; 87 t1 = QUOTE(tbl_name1); 88 t2 = QUOTE(tbl_name2); 89 put t1= t2= ; 90 if tbl_name1 = "&dataset1" and tbl_name2 = "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); 91 /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name 92 Also created new variable for later use*/ 93 else delete; /*delete other records if not satisfy the condition*/ 94 run; 95 96 %mend dynamic_left_join; 97 98 99 options mprint; 100 101 %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST); MPRINT(DYNAMIC_LEFT_JOIN): data UNQ_KEY; MPRINT(DYNAMIC_LEFT_JOIN): set CTL_JOINFLD; MPRINT(DYNAMIC_LEFT_JOIN): length t1 t2 $ 100; MPRINT(DYNAMIC_LEFT_JOIN): t1 = QUOTE(tbl_name1); MPRINT(DYNAMIC_LEFT_JOIN): t2 = QUOTE(tbl_name2); MPRINT(DYNAMIC_LEFT_JOIN): put t1= t2= ; MPRINT(DYNAMIC_LEFT_JOIN): if tbl_name1 = "COUNTERPARTY" and tbl_name2 = "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); MPRINT(DYNAMIC_LEFT_JOIN): else delete; MPRINT(DYNAMIC_LEFT_JOIN): run; t1="COUNTERPARTY " t2="COUNTERPARTY_HIST " t1="COUNTERPARTY " t2="COUNTERPARTY_HIST " t1="LKP_BS_GL_FRP_MAP " t2="LKP_BS_GL_FRP_MAP " t1="LKP_BS_GL_FRP_MAP " t2="LKP_BS_GL_FRP_MAP " NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD. NOTE: The data set WORK.UNQ_KEY has 0 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 959.31k OS Memory 20136.00k Timestamp 08/16/2024 04:07:39 PM Step Count 32 Switch Count 2 Page Faults 0 Page Reclaims 136 Page Swaps 0 Voluntary Context Switches 10 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 272 102 103 options mprint; 104 105 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 115     So, I revised the code to remove the trailing space like this:   data UNQ_KEY; set CTL_JOINFLD; /*Used the table created above*/ length t1 t2 $ 30; t1 = strip(tbl_name1); t2 = strip(tbl_name2); put t1=t2=; if t1 eq "&dataset1" and t2 eq "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name Also created new variable for later use*/ else delete; /*delete other records if not satisfy the condition*/ run; %mend dynamic_left_join; But still I'm getting 0 results. I checked that t1 and t2 do not anymore have trailing spaces.  99 options mprint; 100 101 %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST); MPRINT(DYNAMIC_LEFT_JOIN): data UNQ_KEY; MPRINT(DYNAMIC_LEFT_JOIN): set CTL_JOINFLD; MPRINT(DYNAMIC_LEFT_JOIN): length t1 t2 $ 30; MPRINT(DYNAMIC_LEFT_JOIN): t1 = strip(tbl_name1); MPRINT(DYNAMIC_LEFT_JOIN): t2 = strip(tbl_name2); MPRINT(DYNAMIC_LEFT_JOIN): put t1=t2=; MPRINT(DYNAMIC_LEFT_JOIN): if t1 eq "COUNTERPARTY" and t2 eq "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); MPRINT(DYNAMIC_LEFT_JOIN): else delete; MPRINT(DYNAMIC_LEFT_JOIN): run; t1=COUNTERPARTY t2=COUNTERPARTY_HIST t1=COUNTERPARTY t2=COUNTERPARTY_HIST t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD. NOTE: The data set WORK.UNQ_KEY has 0 observations and 7 variables.     Thank you for the tips on checking the table name. I think I'm getting near to the right solution. ... View more

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 data CTL_JOINFLD; 70 infile datalines dsd /*delimiter=','*/; 71 length tbl_name1 $25. tbl_name2 $25. id $20. prim_key $20.; 72 input tbl_name1 $ tbl_name2 $ id $ prim_key $; 73 datalines; NOTE: LOST CARD. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 79 CHAR .; ZONE 03222222222222222222222222222222222222222222222222222222222222222222222222222222 NUMR 9B000000000000000000000000000000000000000000000000000000000000000000000000000000 tbl_name1= tbl_name2= id= prim_key= _ERROR_=1 _N_=5 NOTE: SAS went to a new line when INPUT statement reached past the end of a line. NOTE: The data set WORK.CTL_JOINFLD has 4 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 668.87k OS Memory 22436.00k Timestamp 08/16/2024 02:24:34 AM Step Count 38 Switch Count 2 Page Faults 0 Page Reclaims 123 Page Swaps 0 Voluntary Context Switches 10 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 79 ; 80 run; 81 82 %macro dynamic_left_join(dataset1,dataset2); 83 84 data UNQ_KEY; 85 set CTL_JOINFLD; /*Used the table created above*/ 86 if tbl_name1 = "&dataset1" and tbl_name2= "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); 87 88 /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name*/ 89 90 /*Also created new variable for later use*/ 91 else delete; /*delete other records if not satisfy the condition*/ 92 run; 93 94 %mend dynamic_left_join; 95 96 options mprint; 97 98 %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST); MPRINT(DYNAMIC_LEFT_JOIN): data UNQ_KEY; MPRINT(DYNAMIC_LEFT_JOIN): set CTL_JOINFLD; MPRINT(DYNAMIC_LEFT_JOIN): if tbl_name1 = "COUNTERPARTY" and tbl_name2= "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key); MPRINT(DYNAMIC_LEFT_JOIN): else delete; MPRINT(DYNAMIC_LEFT_JOIN): run; NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD. NOTE: The data set WORK.UNQ_KEY has 0 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 950.59k OS Memory 22952.00k Timestamp 08/16/2024 02:24:34 AM Step Count 39 Switch Count 2 Page Faults 0 Page Reclaims 156 Page Swaps 0 Voluntary Context Switches 14 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 99 100 options mprint; 101 102 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 112 Hi @ballardw,   For the || operator, I simply wanted to concatenate 'X.' and 'Y.' with the values in prim_key field of CTL_JOINFLD table. e.g. DM_COUNTERPARTY,DM_COUNTERPARTY_HIST,Y,COUNTERPARTY_ID /*This is the first record from the CTL table*/ prim_key = COUNTERPARTY_ID The catx code should generate like this: X.COUNTERPARTY_ID = Y.COUNTERPARTY_ID    I inserted the logs from the run and put the options mprint;. Btw, I'm using SAS ODA. Is there some difference with what you are using?  Since you mentioned that you tried to run and there's observations.  ... View more