topic Re: Merging two datasets, one has a text column with length of 60? in SAS Programming

Merging two datasets, one has a text column with length of 60?

sasDave — Thu, 12 Nov 2020 04:40:48 GMT

SAS 9.4

I'm trying to merge two datasets and one column is giving me issues. This column ("county") is named the same in both datasets, but in one of them it has a length of 8 and in the other a length of 60. I believe this is causing issues with the merge. Any ideas on how I can change the 60 to 8 or 8 to 60?

Re: Merging two datasets, one has a text column with length of 60?

mkeintz — Thu, 12 Nov 2020 05:44:55 GMT

When you are using the MERGE statement for multiple datasets with common variables, the earliest (i.e. leftmost) discovery of the common variable will determine the attributes, like length, of the variable - even though the rightmost value will prevail. So if you want the length of 60 to be propagated to the new dataset, make sure the incoming dataset with the length of 60 is mentioned first in the merge statement.

Let's say you want to merge have1 and have2 with variables ID, A, B, C, X, Y, and Z in common. But you want the length of variables X, Y, and Z from have2 to prevail, but you want the lengths of A, B, and C to be inherited from have1. Then you could:

data want;
  if 0 then set have1 (drop=x y z) have2;
  merge have1 have2;
  by id;
run;

The "if 0" statement is enountered by the sas compiler prior to the merge statement. The compiler resolves it to produce lengths as I described above. But since "if 0" is never true, the "then set ..." action is never performed. Instead the MERGE statement does the actual work, with~~out~~ the lengths of X Y and Z determined by have2.

Re: Merging two datasets, one has a text column with length of 60?

andreas_lds — Thu, 12 Nov 2020 05:45:59 GMT

You should check why the same variable has been defined with different lengths during import and fix the problem in that stage of your project. If you can't change the importing code, ask the one who created it, there must be a reason, maybe you have state-codes in one dataset an state names in the other.

Re: Merging two datasets, one has a text column with length of 60?

Ksharp — Thu, 12 Nov 2020 11:59:31 GMT

data want;
length county $ 60;
merge have1 have2;
by county ;
run;