Query for Merge/ Update

[sanjaymane7]

Hi, I am merging two tables, but blank variables are not being updated in dataset. Example - Month and Policy etc.

Please help in this.

My Code –

data table_C;

 merge test.table_a (in=a) test.table_b (in=b);

 by Policy_No;

 if a;

 run;

Table_A

Month	Policy_No	Insured_Name
Apr-2020	110522023120027725	SHYAM LAL YADAV
		SHYAM LAL YADAV
		SHYAM LAL YADAV
Apr-2020	920222023120620551	SAHIL GUPTA
		SAHIL GUPTA
		SAHIL GUPTA
Apr-2020	920222023120658066	MANOJ KUMAR
		MANOJ KUMAR
		MANOJ KUMAR
Apr-2020	200122028170000374	RAVI VERMA
Apr-2020	200122028170000374	RAVI VERMA
Apr-2020	200122028170000374	RAVI VERMA

Table_B

Month	Policy_No	BusinessTypeActual
Apr-2020	110522023120027725	RollOver
Apr-2020	920222023120620551	RollOver
Apr-2020	920222023120658066	RollOver
Apr-2020	200122028170000374	Fresh Business

Expecting output=

Month	Policy_No	Insured_Name	BusinessTypeActual
Apr-2020	110522023120027725	SHYAM LAL YADAV	RollOver
Apr-2020	110522023120027725	SHYAM LAL YADAV	RollOver
Apr-2020	110522023120027725	SHYAM LAL YADAV	RollOver
Apr-2020	920222023120620551	SAHIL GUPTA	RollOver
Apr-2020	920222023120620551	SAHIL GUPTA	RollOver
Apr-2020	920222023120620551	SAHIL GUPTA	RollOver
Apr-2020	920222023120658066	MANOJ KUMAR	RollOver
Apr-2020	920222023120658066	MANOJ KUMAR	RollOver
Apr-2020	920222023120658066	MANOJ KUMAR	RollOver
Apr-2020	200122028170000374	RAVI VERMA	Fresh Business
Apr-2020	200122028170000374	RAVI VERMA	Fresh Business
Apr-2020	200122028170000374	RAVI VERMA	Fresh Business

[PeterClemmensen · Accepted solution]

How about

data a;
input Month $ 1 - 8 Policy_No $ 10 - 27 Insured_Name $ 29 - 43;
datalines;
Apr-2020 110522023120027725 SHYAM LAL YADAV 
                            SHYAM LAL YADAV 
                            SHYAM LAL YADAV 
Apr-2020 920222023120620551 SAHIL GUPTA     
                            SAHIL GUPTA     
                            SAHIL GUPTA     
Apr-2020 920222023120658066 MANOJ KUMAR     
                            MANOJ KUMAR     
                            MANOJ KUMAR     
Apr-2020 200122028170000374 RAVI VERMA      
Apr-2020 200122028170000374 RAVI VERMA      
Apr-2020 200122028170000374 RAVI VERMA      
;

data b;
input Month $ 1 - 8 Policy_No $ 10 - 27 BusinessTypeActual $ 29 - 43;
datalines;
Apr-2020 110522023120027725 RollOver       
Apr-2020 920222023120620551 RollOver       
Apr-2020 920222023120658066 RollOver       
Apr-2020 200122028170000374 Fresh Business 
;

data c(drop = p m rc);

   if _N_ = 1 then do;
      dcl hash h(dataset : "b");
      h.definekey("Policy_No");
      h.definedata("BusinessTypeActual");
      h.definedone();
   end;

   set a;
   if 0 then set b;

   if Policy_No then p = Policy_No;
   else Policy_No = p;

   if not cmiss(Month) then m = Month;
   else Month = m;

   rc = h.find();

   retain p m;

run;

[Kurt_Bremser]

First carry forward, then join:

data a;
input Month :??monyy8.  Policy_No $ 10 - 27 Insured_Name $ 29 - 43;
format month yymmd7.;
datalines;
Apr-2020 110522023120027725 SHYAM LAL YADAV 
                            SHYAM LAL YADAV 
                            SHYAM LAL YADAV 
Apr-2020 920222023120620551 SAHIL GUPTA     
                            SAHIL GUPTA     
                            SAHIL GUPTA     
Apr-2020 920222023120658066 MANOJ KUMAR     
                            MANOJ KUMAR     
                            MANOJ KUMAR     
Apr-2020 200122028170000374 RAVI VERMA      
Apr-2020 200122028170000374 RAVI VERMA      
Apr-2020 200122028170000374 RAVI VERMA      
;

data b;
input Month :??monyy8. Policy_No $ 10 - 27 BusinessTypeActual $ 29 - 43;
format month yymmd7.;
datalines;
Apr-2020 110522023120027725 RollOver       
Apr-2020 920222023120620551 RollOver       
Apr-2020 920222023120658066 RollOver       
Apr-2020 200122028170000374 Fresh Business 
;

data _a;
set a;
retain _month _policy;
if month ne .
then _month = month;
else month = _month;
if policy_no ne ""
then _policy = policy_no;
else policy_no = _policy;
drop _:;
run;

proc sql;
create table want as
  select
    _a.*,
    b.BusinessTypeActual
  from _a left join b
  on _a.month = b.month and _a.policy_no = b.policy_no
;
quit;

[sanjaymane7]

Thank you