Hello Let's say that there are 2 fields that identify a customer- HighCustID and LowCustID. Let's think about it like a family structure where some people can belong to same family. So, under specific HighCustID there can be multiple LowCustID. Each person (LowCustID) can be Main (Ind_Main=1) or not main (Ind_Main=0) The number of HighCustID is defined as  the number of the main person . For example: In month 202410 , 2 people : 111(Main) and 222 (not main )  belong to same HighCustID  so the number of the HighCustID will be 111 ( as the number of the main) In month 202409, these 2 people  changed the functions and now 111 is not main and 222 is main so the number of the HighCustID will be 222 ( as the number of the main). For each month there are 2 tables:  table in level of HighCustID with data of wealth table that shows the ownership structure   The task is to perform Left join from table t202410_HighLevel   to table t202409_HighLevel. The problem is that the number of HighCustID can be changed during months and I still want to merge them. So, I want to merge 111(t202410_HighLevel) with 222(t202409_HighLevel)   I want to merge 444(t202410_HighLevel) with 444(t202409_HighLevel)   and about 333(t202410_HighLevel)  he doesnt exist in t202409_HighLevel (It is a new person that join )   My question- Here is the code I created and in last step I want to perform merge in the following way:   on a.HighCustID=b.HighCustID OR a.HighCustID=substr(one of the elemetns in IDs from table t202409_HighLevel_b) Can anyone help to write this code that search if  a.HighCustID equal to one of the elements (In concatentate field) from t202409_HighLevel_b? My code is working perfect but there are some things I want to do: In real life It can have more commas in the concatenate and then I want to create merge that automatically check how many commas have and try to merge  with all possible locations . Here is my full code: /***********INPUT data sets*****************/ /***********INPUT data sets*****************/ /***********INPUT data sets*****************/ Data t202410_HighLevel; Input HighCustID wealth; cards; 111 100 333 80 444 180 ; Run; Data t202409_HighLevel; Input HighCustID wealth; cards; 222 110 444 80 ; Run; Data t202410_ownership; Input HighCustID LowCustID Ind_Main; cards; 111 111 1 111 222 0 333 333 1 444 444 1 ; Run; Data t202409_ownership; Input HighCustID LowCustID Ind_Main; cards; 222 111 0 222 222 1 333 333 1 ; Run; /***********Data sets that I created in order to get the wanted data set*****/ /***********Data sets that I created in order to get the wanted data set*****/ /***********Data sets that I created in order to get the wanted data set*****/ data want_ownership_202410; length CAT $20.; do until (last.HighCustID); set t202410_ownership; by HighCustID notsorted; CAT=catx(',',CAT,LowCustID); end; drop LowCustID; run; data want_ownership_202409; length CAT $20.; do until (last.HighCustID); set t202409_ownership; by HighCustID notsorted; CAT=catx(',',CAT,LowCustID); end; drop LowCustID; run; proc sql; create table t202409_HighLevel_b as select a.*,b.CAT from t202409_HighLevel as a left join want_ownership_202409 as b on a.HighCustID=b.HighCustID ; quit; proc sql; create table Want as select a.HighCustID as HighCustID202409,a.wealth as wealth202410,b.wealth as wealth202409 from t202410_HighLevel as a left join t202409_HighLevel_b as b on a.HighCustID=b.HighCustID OR CAT(a.HighCustID)=scan(b.CAT,1,",") OR CAT(a.HighCustID)=scan(b.CAT,2,",") OR CAT(a.HighCustID)=scan(b.CAT,3,",") OR CAT(a.HighCustID)=scan(b.CAT,4,",") ; quit;     ... View more

Hello I want to import EXCEL file that is located in specific location (These are external files that other team create). When I am using Import Wizard then all is working well and I succeed import it  and I see the code that was created : data mydata; INFILE '/usr/local/saswork/SAS_workD18B00007116_LINX107717A14/#LN00389' LRECL=91 ENCODING="HEBREW" TERMSTR=CRLF DLM='7F'x MISSOVER DSD ; INPUT CUST_ID : $CHAR19. Branch : ?? BEST3. Numerator : $CHAR12. Date1 : ?? YYMMDD10. Time1 : ?? TIME8. Date2 : ?? YYMMDD10. Amnt : ?? COMMA18. ProductID : ?? BEST4. ; run; The issue is that in the future I want to write a code (NOT use Import wizard) that import the file. I know that the path that was created (by using import wizard) is a temporary path '/usr/local/saswork/SAS_workD18B00007116_LINX107717A14/#LN00389' When I open Excel file I found the path of the file : "//leumisrv1/Shidurim/AFT/Nihul_Sikunim/Credit_Risks/Actimize_Loans_Reports/Loan_Transaction_031124.CSV" I changed the temporary path to the path that I saw but I see an error that SAS doesnt find this path   I also tried to change the direction on slash (from / to \) and still same error   What should I do please?       ... View more