Hi: I don't think you've provided enough information for a concrete suggestion. In a quick test, using your fake data I created Table A with POLICY and CUSTOMER and then created TABLE B with the same 4 values for POLICY and also created a LIMIT column. I ran a few tests and got matches using both a DATA step MERGE and an SQL join. Table A had POLICY with a length of $9 and B had POLICY with a length of $20. Of course, SAS did give a WARNING about the differing lengths of the POLICY variable in the MERGE where the data set with the length of $9 for policy was listed first. The code I tested is listed at the bottom of this post. You should be able to run it and prove to yourself that there were matches. There are 4 steps -- 2 steps with MERGE and 2 steps with SQL JOIN. The POLICY values were the same and the output was 4 rows each time. So I cannot duplicate your observed behavior. There must be something different about your code or your actual data that is causing the non-matches to happen. Do note the output from PROC CONTENTS after each step. It will make a difference in the output data for the length of POLICY, depending on which table is listed first in the MERGE or the JOIN. Others have suggested sharing more of your actual code and your actual data. I also suggest reviewing the log and reporting the exact warnings or errors you might see there (or even, post the log if it's not too long). However, if the ONLY difference in POLICY is the length, your MERGE or JOIN should work, as illustrated by my examples. Cynthia Data A;
Length policy $9;
Infile datalines dlm=',';
Input policy $ customer $;
Datalines;
000718124,1001
000714946,1002
000910934,1003
000901672,1004
;
Run;
Data B;
Length policy $20;
Infile datalines dlm=',';
Input policy $ limit $;
Datalines;
000718124,20000
000714946,30000
000910934,40000
000901672,50000
;
Run;
Proc sort data=A; by policy; run;
Proc sort data=B; by policy; run;
Data both;
Merge A(in=fromA)
B(in=fromB);
By policy;
If fromA=1 and fromB=1 then output both;
Run;
Proc contents data=both;
Title '1) Data A length $9 listed first in merge';
Run;
Proc print data=both;
Run;
title;
Data altboth;
Merge B(in=fromB)
A(in=fromA);
By policy;
If fromA=1 and fromB=1 then output altboth;
Run;
Proc contents data=altboth;
Title '2) Data B length $20 listed first in merge';
Run;
Proc print data=altboth;
Run;
proc sql;
create table sqboth
as select a.policy, customer, limit
from a,b
where a.policy=b.policy
order by policy;
quit;
proc contents data=sqboth;
title '3) data a listed first in the join';
run;
proc print data=sqboth;
run;
proc sql;
create table altsql
as select b.policy, customer, limit
from b,a
where a.policy=b.policy
order by policy;
quit;
proc contents data=altsql;
title '4) data b listed first in the join';
run;
proc print data=altsql;
run;
... View more
