topic Re: Matching 2 Character Fields in SAS Programming

Matching 2 Character Fields

SASMom2 — Mon, 08 Sep 2025 18:06:03 GMT

I need some help resolving an issue. I am joining two tables by a field called 'policy'.

policy	customer
000718124	1001
000714946	1002
000910934	1003
000901672	1004

In table A the policy field is Type: Character and Length: 9 and in the table B it is Type: Character and Length: 20.

Since the field has 9 characters I changed the length of the field in table B in proc sql like below.

data test1;
length policy $9;
set B;
run;

So now, the field policy in test1 table has length = 9. So, when I join tables A and test1, I do not get any data back.

So, I changed the length of policy field in table A also, even though the character length is 9.

data test2;
length policy $9;
set A;
run;

Again I tried join test1 and test2 but I am still not getting any data back. Both tables have the same values for policy field so I know there is data but I do not get anything back.

Thank you in advance

Re: Matching 2 Character Fields

PaigeMiller — Mon, 08 Sep 2025 18:44:46 GMT

So, when I join tables A and test1, I do not get any data back.

There could be a dozen reasonable explanations for this. Please show us the code you are using. Please show us (a portion of) the data in table A and show us (a portion of) the data in table B.

Re: Matching 2 Character Fields

Cynthia_sas — Mon, 08 Sep 2025 22:11:42 GMT

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;

Re: Matching 2 Character Fields

Tom — Mon, 08 Sep 2025 23:25:39 GMT

Please show the code you used to try and match the records. You mentioned SQL but only showed DATA step code. Did you try to do the matching using SQL?

A couple of issues to check.

When you shorten the length of variable the way you did SAS will take the first 9 characters of the field. If your 9 digit numbers were right aligned in the 20 character variable then the first 9 characters are all spaces.
If the character variables where created by letting SAS autoconvert a numeric variable to character then SAS will use the BEST12 format. Which for the examples you showed would result in POLICY numbers without the leading zeros (but with the leading spaces). So a numeric value like 718,124 would have been converted to ' 718124' instead of '000718124'.

Re: Matching 2 Character Fields

Ksharp — Tue, 09 Sep 2025 07:07:00 GMT

It would be best if you could post some your original data to test the problem you are running into.
Actually the length of variable in these two dataset would not affect the JOIN operator in SQL . If you don't believe it , you could test it on your own.
And I doubt you have some unprintable characters in one dataset ,that is the reason why you can't success to join these two datasets.
Check this:
https://communities.sas.com/t5/SAS-Programming/Cannot-join-by-the-same-key/m-p/971964

Re: Matching 2 Character Fields

Kurt_Bremser — Tue, 09 Sep 2025 08:05:58 GMT

Assign $HEX formats of suitable length (18 and 40) to the policy variables in both datasets and look what you find.
I suspect leading blanks (hex 20) or unprintable characters (0D0A, for instance).