topic Re: Merge error in SAS News and Q&A

Merge error

hwang_000 — Tue, 09 Nov 2021 16:10:22 GMT

I have merge error like below:

the contents(texture format) become shorter than original after i merged 2 different tables, even I've tried both function of 'merge' and 'left join' but result is same.

안녕하세요, 테이블 두 개를 merge 하고 나면 문자열 값이 일제히 짧아집니다, 이렇게 값이 짧아지는 이유가 무엇이며 원본 그대로 merge 하려면 어떡해야 하나요?

Re: Merge error

ballardw — Fri, 31 Jul 2020 15:40:23 GMT

Order of operations in MERGE affect length/values of variables with the same name.

Please see this code:

data example1;
input id text1 :$15.;
datalines;
1 abcdefghijklmn
2 somethingelse
;

data example2;
   input id text1:$5. word $;
datalines;
1 abcde zzzz
2 some  yyyy
;

data merge1;
  merge example1
        example2
  ;
  by id;
run;

data merge2;
  merge example2
        example1
  ;
  by id;
run;

proc contents data=merge1;
run;
proc contents data=merge2;
run;

The first data set encountered in a Merge statement sets the LENGTH of common named variables.

The last encountered data set in a Merge statement sets the Value of common named variables.

So in Merge1 , the Length of the Text1 variable is 15 but the value comes from example2.

In Merge2 the length of Text1 comes from Example2 so the value from Example1 gets truncated because it cannot fit.

One fix might be to set the length of the variable before the Merge statement.

data merge3;
  length text1 $ 15;
  merge example2
        example1
  ;
  by id;
run;

Re: Merge error

AmeeKang — Mon, 03 Aug 2020 01:40:41 GMT

안녕하세요

merge문은 컬럼명이 같은 변수가 나오면,

length는 처음 데이터셋의 것을 따라가고, 컬럼값은 마지막 데이터셋의 값을 따라갑니다.

data ex1;
input id score $3.;
datalines;
1 160
2 170
3 180
;

data ex2;
input id score $4.;
datalines;
1 1601
2 1701
3 1801
;

data merge1;
merge ex1 ex2;
by id;
run;

위의 코드는 length는 ex1(score value: 160,170,180; length는 3자리)을 따라가고,
컬럼값은 마지막 데이터셋의 값 ex2(score value: 1601,1701,1801 4자리)를 따라갑니다.

1601,1701,1801의 값이 length값이 3자리로 지정됨에 따라 잘리게 됩니다.

해결방법

data merge2
length score $4.;
merge ex1 ex2;
by id;
run;

merge code에 length를 지정합니다.