"Multiple lengths were specified" warning message at merge is not solved with length statement

[emera86]

Hi everyone,

I'm having a recurrent problem with different codes when I try to merge different datasets.

If the merged variables have different lengths for each original dataset, a warning message will appear to help you detect and avoid problems with truncation. That's fine and very useful, but I would like to avoid this message now that I'm sure that this is not ruining my results.

For this I have been searching in this community for a solution and I found an easy a definitive one: declaring the length of the problematic variables before the 'merge' statement as in this example taken from a paper I found:

data merge1c;
length lastname $10;
merge employees salaries;
by lastname;
run;

However, even when I define a length greater than the original lengths for the variables I still got this message. I don't understand why this is not solving the issue. Is there any other issue that may be triggering this warning message?

Thank you in advance for your help!

[PaigeMiller]

I don't think that the WARNING message in the log should be viewed as an indication of a problem here. If you have chosen the LENGTH statement properly, there will be no truncation.

If you really want to get rid of this WARNING, you can use the VARLENCHK=NOWARN option. https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lesysoptsref&docsetTarget=n0x...

To tell you the truth, if you are going to use this option to turn off warnings, I would turn it back on (so the warnings appear normally) immediately after this DATA step runs, as the WARNING could be useful to have elsewhere in your program.

[emera86]

Thank you @PaigeMiller, but I would like to try other solutions before surrender to the NOWARN option...

[PaigeMiller]

---

@emera86 wrote:

Thank you @PaigeMiller, but I would like to try other solutions before surrender to the NOWARN option...

---

It's not clear what solution you would want. Please describe a satisfactory solution, as you see it.

[andreas_lds]

The message is displayed because you shorten the length of the variable. SAS just checks the length, not if you will or will not lose any data. Before disabling the warning message, you should be sure that even in future using the code no truncation happens.

[FreelanceReinh]

Hi @emera86,

---

@emera86 wrote:

(...) Is there any other issue that may be triggering this warning message?

---

This is likely the case and the reason why I would not feel comfortable with this warning message. To investigate this, can you please run the diagnostic DATA step below (adapted to your metadata, i.e. dataset names etc.) and post the log of it?

data _null_;
if 0 then set employees(rename=(lastname=_n1))
              salaries (rename=(lastname=_n2));
_w1=lengthc(_n1);
_w2=lengthc(_n2);
put _w1= _w2=;
stop;
length lastname $10;
merge employees salaries;
by lastname;
run;

[Kurt_Bremser]

To properly fix this, set up a data dictionary that describes the attributes of variables, and use that whenever you write code that imports data into SAS. If you use the option to turn off the WARNING, another problem in the same step that you did not anticipate will bite you in your behind.

[emera86]

Dear @Kurt_Bremser,

I totally agree, that's why I was trying to find a different solution!

[Kurt_Bremser]

A possible "after the fact" fix is this:

Inspect all your input datasets for common variables, and retrieve the maximum length encountered. You can do this from DICTIONARY.COLUMNS in SQL.

Then create a LENGTH statement that sets these maximum lengths, and insert it before any SET or MERGE statements. This will make sure that no truncation can happen.

But like duck tape is used in NASCAR to fix the damage from an accident and not to set up a car before the race, you should properly prepare your data on import into the SAS system and not fix it when an "accident" happens.

[Astounding]

If you are certain that you have determined the proper length to use, fix the individual data sets first:

data employees_fixed;
   length lastname $10;
   set employees;
run;
data salaries_fixed;
   length lastname $10;
   set salaries;
run;

This fix should generate no warning messages, even if values are being truncated.  And your future merges using the new data sets will not encounter length differences.

[emera86]

Dear @Astounding,

That is not working, I get the same error when I perform the separated data steps 😞

Something more elaborate as

data employees_fixed;
   length lastname10 $10;
   set employees;
   lastname10 = lastname;
run;

is working, but I was hoping for a more straightforward solution...

Thank you for your help anyway!