BookmarkSubscribeRSS Feed
BIGuidance
Fluorite | Level 6

When merging 2 tables on a common by-variable you run the risk of having a different length on this (or these) by variable(s). If this is the case, SAS will casually inform you with the following warning:

WARNING: Multiple lengths were specified for the BY variable mergevar by input data sets. This may cause unexpected results.

On first sight you might be inclined to ignore this pretty warning but SAS will do exactly as it states: it may cause unexpected results. In some cases the merge might be successful, other times the merge is partial or just not executed at all, leaving you with an empty table.

Take the following example:

DATA table1;

          length mergevar $10;

          mergevar = "blabla";

          table1varIwant = "interesting stuff";

RUN;

DATA table2;

          length mergevar $11;

          mergevar = "blabla";

          table2varIwant = "more interesting stuff";

RUN;

PROC SORT DATA=table1;

          BY mergevar;

RUN;

PROC SORT DATA=table2;

          BY mergevar;

RUN;

DATA combinedtable;

          MERGE table1 table2;

          BY mergevar;

RUN;

The warning will be shown in the log.

WARNING: Multiple lengths were specified for the BY variable mergevar by input data sets. This may cause unexpected results.

Hopefully you will get the correct result but if you have a lot of observations to merge on, changes are… you won’t.

One way to solve this is by first determining the longest length for the ‘mergevar’ and using that information in a new length statement just before the actual merge.

The PROC SQL puts the longest length in a macro variable calledmaxlength’.

PROCSQL;

          SELECT max(length) INTO :maxlength

                   FROM sashelp.vcolumn

                             WHERE libname='WORK'

                                      and memname in

                                      ("table1", "table2")

                                      and upcase(name)="mergevar";

QUIT;

This macrovariable is than used to set the LENGTH of the mergevar.

DATA combinedtable;

          LENGTH mergevar $ &maxlength;

          MERGE table1 table2;

          BY mergevar;

RUN;

This merge runs without warning and you should have the expected result.

8 REPLIES 8
FriedEgg
SAS Employee

You could also just use the option varlenchk=nowarn

DouglasMartin
Calcite | Level 5

That only suppresses the warning message. It doesn't solve the problem that resulted in the warning message.

FriedEgg
SAS Employee

Certainly it does not 'solve' anything it just suppresses the warning in situations when you know there is no problem needing to be solved.  You can also list the dataset with the larger length first in the merge statement to have the resulting dataset with the longer of the given attributes for the common variables.

JohnT
Quartz | Level 8

Gday,

I think there are two other ways it could be done:

DATA combinedtable;

length mergevar $11.;

MERGE table1 table2;

BY mergevar;

RUN;

^^

Specify the biggest length prior to the merge statement

DATA combinedtable;

  MERGE table2 table1 ; 

  BY mergevar;
RUN;

^^
If you know that table2 has the bigger variable put it first in the merge statement.

Ksharp
Super User

Why not direct to use the max length of character variable.

DATA combinedtable;
LENGTH mergevar $ 32767;
MERGE table1 table2;
 BY mergevar;
RUN;

Ksharp

MikeZdeb
Rhodochrosite | Level 12

Hi ... if anyone would like to do some reading (from 9 years ago with most of the same points brought up in the discussion) ...

"Danger:  MERGE Ahead! Warning:  BY Variable with Multiple Lengths!"

by Bob Virgile

http://www.nesug.org/proceedings/nesug03/at/at005.pdf

Haikuo
Onyx | Level 15

Thanks, Mike. I have read one of his books on improving SAS code efficiency, and I have benefited from it a lot!

Regards,

Haikuo

AndersS
Pyrite | Level 9

A late comment:
In general, when you fetch a lot of data from a big DWH, you want an ABEND if ANYTHING is incorrect. Then you can sit down and consider the structure of your tables, and what problem you really want to solve.

  

There are of course several technical ways of avoiding the the error message, but they do not really help you to get accurate and realiable SAS-programs.

Please note that the use of LENGTH mergevar $ 32767; will increase the amount of computer resorces used.

Regards  Anders

Anders Sköllermo (Skollermo in English)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 9150 views
  • 1 like
  • 8 in conversation