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 |
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.