엑셀로 저장된 데이터를 SAS에서 proc import로 읽어올 때 이런저런 어려움을 호소하시는 분들을 자주 보게 되는데요..
경험은 그리 많지 않지만.. 제가 겪었던 문제와 그에 대해 나름대로 찾았던 해결책들을 저의 상상력도 동원하면서 두서없이 말씀드려볼까 합니다.
("종합정리"라는 취지를 살리려고 전에 다른 분들이 질문한 것에 대해 답변드렸던 것도 같이 포함해서 정리했습니다)
1.
먼저 엑셀과 SAS의 차이점부터 명확히 이해를 하셔야 할 것 같은데요.
SAS 데이터는 변수(=column)와 관측치(=row)로 구성이 되는데, 변수단위로 동일한 서식(format)을 가집니다.
그러니까.. x라는 변수의 두번째 row는 길이가 10인 문자이고, 세번째 row는 소수점 둘째자리까지 표시되는 숫자.. 일 수 없는거지요.
따라서 한번 변수의 유형/서식이 정해지면.. 그 변수는 처음 관측치부터 마지막 관측치까지 동일한 서식을 가져야 합니다.
반면, 엑셀데이터는 column과 row로 구성된 테이블의 형태를 가지고 있지만 각각의 Cell이 독립적으로 서식(format)을 가질 수 있기 때문에, column/row마다 일관적이지 않은 데이터의 유형이 섞여있을 수 있습니다. 오히려 섞여있는 것이 더 일반적입니다.
이 차이때문에 SAS뿐만이 아니고 엑셀데이터를 DB의 테이블형태로 이용하려는 사람들은 데이터의 교환에 어려움을 겪을 수 밖에 없는 겁니다.
2.
한편, MS사의 사람들은 오피스프로그램과 윈도우프로그램을 더 많이 팔기 위해 다른 프로그램에서도 엑셀데이터를 쉽게 읽을 수 있도록 하는 기능, 이른바, MS Jet엔진이라는 것을 만들었는데요. 이것을 만들 때, 위에서 말씀드린 것과 같이 엑셀이 SAS 등과 같은 다른 일반적인 Data와 구조적으로 충돌하는 문제에 대해 많은 고민을 한 것 같습니다.
그 고민의 결과, SAS와 같은 프로그램이 Jet엔진을 호출해서 엑셀데이터를 읽어줄 것을 요청하면, Jet엔진은 해당데이터와 함께 변수의 유형(문자냐 숫자냐의 여부)에 대해 적절히 판단해서 그에 대한 정보도 함께 보내 주기로 한 것 같습니다.. 이를 통해 SAS를 비롯한 다른 프로그램들이 문자/숫자의 여부를 판단할 수 있도록 하려는 것이지요.
3.
그렇다면. MS Jet엔진은 엑셀데이터에 대해 무엇을 근거로 숫자/문자의 여부를 판단하느냐를 알 필요가 있는데요..
저도 MS Jet엔진에 대해서는 잘 모르지만..
그것은 바로 엑셀에서 데이터가 들어 있는 영역(변수명 제외)의 최초의 8개 row를 보고 과반수로 의사결정하는 겁니다.
제가 Jet엔진에 대해서 잘 안다면 논란의 소지없이 정확히 설명드리겠지만... 거기까지는 저의 한계를 넘고, 대신 여러 형태로 엑셀데이터를 저장해보면서 테스트한 결과를 말씀드리면 다음과 같습니다.
1) 8개 row 중, Numeric이 과반수 이상이면, 숫자변수로 판단합니다. 예를들어 Numeric과 String이 5개, 3개이면 당근 숫자이고요. 똑같이 4개인 경우도 숫자변수입니다.
2) 아무것도 들어있지 않은 빈 Cell은 기권처리(^^)합니다. 따라서 Numeric 2개, String 2개, 빈셀 4개 인 경우 숫자입니다.
3) 8개 Row 전체가 비어 있는 경우는 엑셀의 해당 셀서식의 우열을 가지고 판단합니다.
즉, 8개의 Cell 포맷 중에 숫자서식이 많으면 숫자변수입니다. 그렇지만 숫자와 문자서식이 동수이면 이번에는 문자로 취급합니다.
여기서 엑셀의 Default 서식인 "일반"서식은 문자서식으로 취급합니다. 따라서 8개 row가 다 비었으면 웬만하면 문자로 잡힙니다.
참고로 통상적으로 SAS에서 숫자변수의 Missing으로 처리하는 "."에 대해서 Jet엔진은 무식하게도(^^) 문자로 봅니다.
따라서 SAS에서 사용하기 위해 엑셀에 데이터를 입력하실 때, 숫자변수의 미싱 값은 "."을 찍지 마시고 그냥 비워놓으세요. 잘못하면 엉뚱하게 문자로 인식될 우려가 있으니까요..
또, 문자냐 숫자냐의 판단기준이 되는 "8개의 row" 에서 "8"이라는 숫자는 사용자가 바꿀 수도 있는데요..
그것은, 레지스트리의 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 에서, TypeGuessRow 항목이 가지는 값을 원하시는대로 바꾸시면 됩니다.
(그렇지만 System에 대해 잘 아시는 분이 아니시라면 수정하는 것은 별로 권해드리고 싶지는 않습니다. 확인 정도만.. ^^; 엉뚱한 것을 손댓다가는 시스템이 문제를 야기할 수가 있으니까요.)
4.
Jet엔진을 통해 SAS는 변수명, 숫자/문자 구분과 함께 데이터값을 넘겨 받는데... 그 다음부터 조금 어려운 문제가 있습니다.
바로 변수의 길이와 관련된 문제인데요...
복잡하게 변수길이는 왜 따지느냐. 그냥 읽어서 SAS데이터로 저장하면 되지 않느냐! 하는 의문을 가지실 수도 있는데... 그 이유는 SAS 데이터의 구조상 반드시 필요하기 때문입니다.(라이브러리에서 데이터셋에서 view columns 해 보시면 길이를 확인하실 수 있습니다.)
즉, 변수의 길이는 다른 일반적인 랭귀지에서와 마찬가지로 메모리에 기록하고 계산을 하기 위해 반드시 필요한 사항이고, DB기능을 가진 데이터파일이라면 읽고 쓸 때의 효율성 면에서도 반드시 결정되어야 하는 것입니다.
일반적인 경우로 보더라도 데이터 구조상 "길이"에 대한 정보가 없는 데이터 형태는 순차적 접근(sequential access) 밖에 할 수 없기 때문에 워드같은 부정형의 일반적인 문서에 적합한 형태이고, SAS나 기타 DB의 기능을 조금이라도 가진 프로그램에서 사용하는 데이터셋/테이블들은 직접 접근(random/index access)과 프로그램 내부에서의 공간 할당을 위해 길이정보를 함께 포함하고 있습니다.
암튼, 엑셀데이터를 가져옴에 있어서.. 숫자변수의 경우에는 SAS가 기본적으로 8바이트의 길이를 할당하고 웬만한 숫자는 다 8바이트를 이용하여 표현이 가능하기 때문에 두 프로그램 사이에 문제될 소지가 크게 없습니다만, 문자변수의 경우는 한 글자가 1바이트를 차지하기 때문에 문자변수의 길이가 변수값들 중에 가장 긴 문자의 길이보다 크지 않으면 문자를 제대로 담지 못하게 되어 정보가 손실되는 문제가 발생하게 됩니다.
따라서 정보의 손실이 없을려면 처음부터 끝까지 데이터를 다 읽어본 다음에, 가장 긴 문자열을 찾아서 그것으로 문자변수의 길이를 정해야 하는데... 이렇게 되면 다시 난감해집니다. 왜냐하면 상당한 위험을 감수하고, Jet엔진을 통해 첫 8개 row를 이용해서 문자/숫자를 따져서 변수의 형을 결정하는 방법을 사용한 것이 아무 소용이 없어지게 되는 겁니다.
(참! 왜 굳이 이와 같은 상당한 위험을 감수하느냐...는 확실하지는 않지만, 속도문제가 가장 큰 것 같고, 데이터 스텝에서 문자변수의 길이를 정하는 방식도 비슷하기 때문인 것 같습니다. 이에 대해서는 바로 아래에 정리했으니 참고하세요.)
위와 같은 이유로 해서 SAS는 Jet엔진이 주는 문자/숫자정보와 마찬가지 개념으로 문자변수의 길이를 결정하는 방법을 그대로 쓰기로 하고 문자변수의 길이를 최초 8개 row의 길이 중 가장 큰 것을 채택하기로 한 겁니다.(상상입니다.^^)
(그러나 버전 8.1에서는 일괄적으로 255바이트를 주었었을 겁니다. 당시.. "야.. 이거 뭐 이래!" 했었고, 얼마 후 8.2를 설치한 다음 테스트해 보고, "흠. 제대로 고쳤군!" 했던 기억이 있거든요.)
-----------------------------
(*주) - SAS의 문자변수 길이 결정 방식
SAS는 문자변수의 길이를, 맨 처음 데이터스텝에서 데이터가 생성될 때, 해당 변수가 최초로 등호의 왼쪽에 위치할 때, 등호 오른쪽의 결과가 만들어 내는 문자의 길이로 결정하거든요
예를 들어...
data tt;
a="a";b=a||"b";output;
a="aaaa";b="abcd";output;
run;
proc print;run;
을 실행해 보시면, 다음과 같은 결과를 얻으실텐데요.
Obs a b
1 a ab
2 a ab
위와 같은 결과가 나타나게 된 이유는,
데이터스텝의 두번째 줄인 a="a";b=a||"b";output; 에서 a라는 문자변수가 등호의 오른쪽에 최초로 등장하므로, a="a"에 의해 a의 길이가 1로 결정되고,
b의 길이도 마찬가지로 a 다음의 b=a||"b" 에 의해 a의 길이인 1에 "b"의 길이 1이 더해져서 2로 결정되었기 때문에,
이하의 문장에서 a와 b에 아무리 긴 문자열을 주려고 해도 받을 수가 없었기 때문입니다. 데이터셋의 컬럼정보를 확인해 보시면 분명히 아실 수 있을 겁니다.
(input을 통해 읽어들이는 경우는, informat 등으로 길이가 정해지면 당연히 그 길이를 가지게 되고, 그렇지 않은 경우에는 길이가 8로 정해집니다.)
-----------------------------
5.
그런데 이러한 결정이 문제를 야기시켰습니다.
바로 한국/일본/중국처럼 2바이트 문자체계를 사용하는 나라에서...
이를 자세히 설명드리면..
엑셀을 보면, 문자의 길이를 나타내는 함수가 두 개 있는데... 하나가 len() 이고 다른 하나가 lenb() 함수입니다.
=len("가나다") 하시면 "3" 이 나올 겁니다. 그러나 =lenb("가나다")하시면 "6" 이 나옵니다.
=len("123가")하시면 4가 나오는데.. =len("123가")하시면, 5가 나옵니다.
즉, len함수는 한글 1글자의 길이를 1로 취급하는데.. lenb함수는 문자의 길이를 바이트단위로 셉니다.(한글 한 글자가 2바이트라는 것은 다 아시지요?)
그런데.. SAS가 이용하는 Jet엔진의 정보는 len()함수의 계산방식을 따라 변수의 길이를 제공하는 것 같습니다.(이것도 상상 ^^;)
SAS 8.2 는 이 정보를 곧이 곧대로 믿고 변수명의 길이를 채택했었는데... 아뿔사!ㅠㅠ
"가나다" 의 변수길이가 3으로 설정되게 되어버린 거지요. SAS는 기본적으로 바이트단위로 변수의 길이를 사용하니까요.
그 결과 "가"와 "나"의 반쪽만이 SAS 데이터셋으로 들어가게 된 겁니다.
모든 변수값이 영문으로만 되어 있는 미국에서는, 이런 문제는 발생하지 않을 것이므로 디버깅과정에서 걸러지지 않았던 것 같습니다.
6.
암튼.. SAS는 이러한 문제점를 발견하고... 패치프로그램을 통해 다시 8.1의 시대로 돌아갔습니다. 즉, 모든 문자변수의 길이를 255바이트로 주는 겁니다.
proc import를 이용해서 데이터를 읽어들여서 작업을 하시는 분은,
http://support.sas.com/techsup/unotes/SN/006/006709.html
을 참조하시고, proc import의 버그를 수정해주는
http://ftp.sas.com/techsup/download/hotfix/DBCS/v82/oledb/82ol05/win/82ol05wn.exe
을 다운받으셔서 설치하시는 것이 좋을 듯합니다.
하지만, 이것도 문자의 길이가 긴 데이터에서는 완벽한 해결책이 되지 못하는데요... 여전히 다음의 문제점을 안고 있더군요..
(1) len함수로 따졌을 때에는 256이내이지만, lenb함수로 따졌을 때에는 256이상인 경우에도 여전히 255로 잡혀서 문자가 짤립니다..
(2) 최초 8개 row에서 len함수로 따졌을 때 256이상인 문자는 Jet엔진이 문자/숫자 형을 판단하는데 기권처리됩니다. 즉, 예를들어 최초 8개 row에 numeric이 1개이고 나머지가 모두 string인데 이들 문자들의 길이가 모두 len으로 따져 256이상이라면.. 숫자 하나에 기권이 7이므로 그 변수는 숫자로 인식된다는 것입니다. ^^; Jet엔진의 버그 같은데.. 확실치는 않네요.
한편, 8번째 row 이내에 len으로 따져 256보다 큰 문자가 들어있고 우열을 따져 해당 column이 문자변수로 인식했다면, SAS는 묘한 행동을 합니다. ^^;
즉, "긴장모드! 뭔가 큰 놈이다!" 하고, 지금까지의 원칙을 깨고 처음부터 끝까지 엑셀을 다 읽고난 다음 변수의 길이를 결정하더군요.
전체관측치에서 가장 긴문자의 len함수값 곱하기 2로..
왜 곱하기 2를 했을까는, 추측하시겠지만, 실제 길이인 lenb()를 쓸 수는 없기 때문에 최악의 상황, 즉 모두 2바이트문자인 것으로 가정을 하고 그만큼 크게 잡아주는 것 같습니다.(이때문에 위에서 Jet엔진에서 lenb가 아닌 len함수로 계산한 변수의 길이를 제공한다고 상상한 것입니다. ^^)
또 전수조사를 해서 변수의 길이를 따질 때, 해당 문자변수 뿐 만이 아니고 같이 들어있는 다른 문자변수들까지 다 조사해서 각 변수의 길이를 각각의 최대 len값 곱하기 2로 변수의 길이를 결정해줍니다. 즉, 8 row 이내에 256이상의 문자가 들어있는 변수 덕분에 조금 시간은 걸리지만, 다른 문자변수들까지도 무조건 255바이트가 아닌, 적절하게 변수의 길이가 결정되는 겁니다.
결국... 문자를 읽어오는데 한글이 포함된 문자가 잘리는 경우가 발생하면.. 반드시 패치를 하셔야 하고요..
길이가 256글자를 넘는 경우 관측치가 포함되는 경우에는 해당 관측치를 최초의 8 row 이내에 배치하셔서 SAS로 하여금 전수조사를 하도록 유도하셔야 합니다.
그리고 패치 후 엑셀에서 읽은 문자변수의 길이가 모두 255바이트로 되어서 너무 불편하다고 판단되시면...
흠... 할 수 없습니다. 일일이 해당 변수의 최대길이를 조사하신 후, 그 길이를 가지는 새로운 변수를 정의하신 다음, 데이터를 옮기셔야 합니다. ㅠㅠ
그리고, 만약에 문자 변수간의 붙이는 연산(concaternation)이 필요할 때, 변수의 길이 중 값이 들어있지 않은 부분은 모두 공란으로 인식되기 때문에 compress함수등으로 공간을 없애셔야 원하시는 값을 얻으실 수 있을 겁니다.
7.
다음으로는 사용자의 실수로부터 야기되는 문제입니다.
엑셀파일을 열어보면, 멀쩡하게 숫자 값이 들어 있는데도 미싱(.)으로 나오는 경우와,
코드성격을 가진 변수로서 분명히 문자형식으로 입력을 했고 대부분의 값들은 다 문자형식으로 들어오고 있는데 일부의 값들이 공백으로 나오는 경우를 많이 경험하게 되는데요..
이렇게 된 이유는 위에서 말씀드린 것처럼 문자나 숫자변수의 유형이 바뀐 것이 아니라면,
눈에는 숫자로 보이지만, 실제로 그 값은 문자이기 때문이고(예를들어 어포스트로피가 앞에 붙은 숫자 또는 텍스트포맷하에서 입력된 숫자),
반대로 눈에는 다른 문자코드와 같이 보이지만 실제로는 숫자이기 때문입니다. (SAS에서 숫자에 대한 미싱은 "."으로 표현되지만, 문자의 미싱은 그냥 공백입니다)
즉, 엑셀에서 다른 값들과 문제 셀의 서식이 다르게 입력되었기 때문입니다.
이런 경우 사후적으로 엑셀의 셀서식을 바꾸어봐야 값 자체를 바꾸는 것이 아니므로 소용이 없고, 실제 해당 값을 문자에서 숫자로, 또는 숫자에서 문자로 바꾸어 주셔야 합니다...
(따라서 엑셀에서 데이터를 입력하실 때 실수를 줄이기 위해서는 사전에 컬럼 전체를 블럭으로 잡고, 원하시는 서식으로 잡아놓으신 다음에 입력하시는 것이 좋습니다)
그 방법으로는 (엑셀의 기능 중 제가 알고 있는 범위내에서 말씀드리자면) 다음의 몇가지 방법이 있습니다.
(1) 데이터 - 텍스트 나누기 : 엑셀 메뉴의 데이터-텍스트 나누기에서 서식을 정해주는 기능을 사용하면 됩니다. 이 방법은 한번에 한 컬럼씩 밖에 처리할 수 없는 단점이 있지만, 마법사를 통해 간편하게 사용할 수 있고 확실한 결과를 보장합니다.
변환을 원하시는 컬럼(영역)을 선택하신 다음, 데이터 - 텍스트나누기 클릭 후, 마법사에서 1,2단계는 적당히 선택(단, 실제 텍스트를 몇개의 컬럼으로 나누려는 의도로 하는 것이 아니므로 필드가 분할되지 않도록 해야 합니다)하시고, 3단계의 "열데이터 서식"에서 원하시는 숫자/문자서식을 지정해 주시면 됩니다.
(2) 복사 - 선택하여 붙여넣기 : 이 방법은 문자열로 저장된 숫자를 실제 숫자값으로 만들어 주는 경우에 매우 유용합니다. - 적극 추천... ^^;
아무 셀이나 값이 들어있지 않은 빈 셀을 "복사"하신 후, 문자 값에서 실제숫자로 변환해야하는 셀들이 들어 있는 영역을 블럭으로 잡은 다음, 편집메뉴의 "선택하여붙여넣기"(또는 오른쪽마우스클릭 후 나오는 "선택하여 붙여넣기")클릭 후, 팝업창의 "연산"의 "더하기"를 선택한 다음, "확인"하시면 됩니다.
참고로 엑셀의 도움말에서, "텍스트로 저장된 숫자를 숫자로 변환"을 검색하면 위에서 말씀드린 것과 비슷하지만, 1을 곱하는 방법을 이용하여 숫자로 변환하는 과정을 예로 보여주고 있는데, 이 방법에 의한 결과는 0과 미싱을 구별하지 못하는 문제점을 가지게 됩니다. 주의하세요.
(3) 함수이용 - 숫자나 문자열을 Value()함수, Text()함수 등을 이용하여 새로운 영역에 변환한 후, 복사-선택하여 붙여넣기(값 복사)하면 됩니다. 이 경우 위에서 1을 곱할 때와 마찬가지로 value()함수는 미싱과 0에 혼란이 없는 범위 내에서 사용할 수 있습니다.
8.
마지막으로 날짜형식에 대한 것인데요..
엑셀과 SAS는 특점시점을 시작점으로 잡아 일련번호로 부여하는 방식으로 날짜를 관리하고 있습니다.
그러나 기준이 되는 "특정시점"은 서로 다릅니다.
EXCEL에서는 1900년 1월 1일을 기준으로 잡고 1부터 시작합니다. 따져보니까 2000년 1월 1일은 36526 이 되고, 오늘(2004년 7월 1일) 은 38169가 되네요.
(아시겠지만, 셀서식에서 정해지는 표현 방식에 따라 이것이 2007-07-01도 되고 01Jul04도 되고 38169도 되는 것이지요.)
그리고 시간은 하루(=1)를 86400초로 나누어 소숫점자리를 사용하여 시간을 표시합니다. 즉 2004년 7월 1일 01시 39분 31초는 엑셀에서는 38169.06911로 표시되는데, 2004년 7월 1일이 38169이고, 01시 39분 31초는 01*60*60 + 39*60 + 31 = 5971 초이므로 이를 86400으로 나누면 0.06911 을 얻게 되는데 결국 둘을 더해 38169.06911이 되는 것입니다.
반면 SAS에서는 1960년 1월 1일을 0으로 시작해서 날짜를 카운트합니다.
그런데 엑셀과는 달리 날짜를 따지는데 두가지 방식을 씁니다.
하나는 엑셀과 마찬가지로 하루에 1씩 증가시켜서 계산하는 것과 다른 하나는 초단위로 세서 하루에 86400 (= 24 * 60 * 60) 씩 증가하는 것이지요.
따라서 하루를 1로 따지면 2004년 7월 1일은 16253 이고 86400초로 따졌을 때 2004년 7월 1일 01시 39분 31초는 1404265171 입니다.
SAS는 이 두 방식을 서로 엄격히 분리시켜 관리하기 때문에, 예를들어 put d date9.; 과 put d datetime20.; 은 같은 d라 할지라도 전혀 다른 날짜를 출력합니다.
그리고 이 두가지 형식의 관리체계는 DATEPART(), TIMEPART(), DHMS() 등의 함수를 통해 호환됩니다.
암튼..
이렇게 시작시점이 다르고 관리방식에 차이가 있기 때문에 두 프로그램 상에 데이터 호환의 문제가 발생할 여지가 있습니다.
날짜서식만 정확히 지켜진다면, 즉 엑셀에서 셀서식이 날짜형식을 가지고 있거나, SAS에서 데이터가 날짜 format으로 저장되어 있다면
SAS가 import할 때와 export할 때 적절히 변환시켜주기 때문에 날짜 자체에는 문제 없습니다만,
import할 때 SAS의 엄격히 2원화된 날짜관리방식때문에 사용자의 오해를 자주 일으킵니다.
즉, 엑셀에서 날짜서식을 가지고 있는 데이터는 SAS가 date informat이 아니라 datetime informat으로 즉, 86400초단위로 되어 있는 날짜체계로 변환해서 읽어들여 온다는 것입니다.
따라서 SAS에서 출력할 때, 일반적으로 사용하는 date format으로 출력하면 위에서 말씀드린 것과 마찬가지로 엉뚱한 날짜가 출력되는 것입니다.
이럴 때에는 viewtable을 통해 읽어들여온 데이터셑이 과연 어떠한 포맷을 가지고 있는지를 확인하시고, 초단위까지 값을 가진다면 datetime format을 이용하시거나 위에서 말씀드린 함수를 사용하시면 해결될 겁니다. 참고로 datetime 포맷은 SAS 도움말에서 "formats" 또는 "informats" 색인을 이용해서 찾아보실 수 있습니다.
--------
지금까지 장황한 설명이지만 "SAS에서 엑셀데이터를 읽어오는데 왜 이렇게 문제가 많나!" 하는데 대해서는 대충 이해를 하셨을 것 같고요...
SAS가 가진 한계점도 있지만.. 어느정도는 데이터 형식의 차이에서 오는 불가피한 것이다라는 점을 이해해주시면 될 것 같습니다.(저.. SAS회사하고는 관련없습니다! ^^)
제가 확실히 알지도 못하고 너무 장황하게 쓴 것 같습니다. 감안해서 보셔야 할 겁니다.
잘못된 정보를 발견하시면 말씀해 주십시요. 즉시 잘못을 바로 잡겠습니다.
경험은 그리 많지 않지만.. 제가 겪었던 문제와 그에 대해 나름대로 찾았던 해결책들을 저의 상상력도 동원하면서 두서없이 말씀드려볼까 합니다.
("종합정리"라는 취지를 살리려고 전에 다른 분들이 질문한 것에 대해 답변드렸던 것도 같이 포함해서 정리했습니다)
1.
먼저 엑셀과 SAS의 차이점부터 명확히 이해를 하셔야 할 것 같은데요.
SAS 데이터는 변수(=column)와 관측치(=row)로 구성이 되는데, 변수단위로 동일한 서식(format)을 가집니다.
그러니까.. x라는 변수의 두번째 row는 길이가 10인 문자이고, 세번째 row는 소수점 둘째자리까지 표시되는 숫자.. 일 수 없는거지요.
따라서 한번 변수의 유형/서식이 정해지면.. 그 변수는 처음 관측치부터 마지막 관측치까지 동일한 서식을 가져야 합니다.
반면, 엑셀데이터는 column과 row로 구성된 테이블의 형태를 가지고 있지만 각각의 Cell이 독립적으로 서식(format)을 가질 수 있기 때문에, column/row마다 일관적이지 않은 데이터의 유형이 섞여있을 수 있습니다. 오히려 섞여있는 것이 더 일반적입니다.
이 차이때문에 SAS뿐만이 아니고 엑셀데이터를 DB의 테이블형태로 이용하려는 사람들은 데이터의 교환에 어려움을 겪을 수 밖에 없는 겁니다.
2.
한편, MS사의 사람들은 오피스프로그램과 윈도우프로그램을 더 많이 팔기 위해 다른 프로그램에서도 엑셀데이터를 쉽게 읽을 수 있도록 하는 기능, 이른바, MS Jet엔진이라는 것을 만들었는데요. 이것을 만들 때, 위에서 말씀드린 것과 같이 엑셀이 SAS 등과 같은 다른 일반적인 Data와 구조적으로 충돌하는 문제에 대해 많은 고민을 한 것 같습니다.
그 고민의 결과, SAS와 같은 프로그램이 Jet엔진을 호출해서 엑셀데이터를 읽어줄 것을 요청하면, Jet엔진은 해당데이터와 함께 변수의 유형(문자냐 숫자냐의 여부)에 대해 적절히 판단해서 그에 대한 정보도 함께 보내 주기로 한 것 같습니다.. 이를 통해 SAS를 비롯한 다른 프로그램들이 문자/숫자의 여부를 판단할 수 있도록 하려는 것이지요.
3.
그렇다면. MS Jet엔진은 엑셀데이터에 대해 무엇을 근거로 숫자/문자의 여부를 판단하느냐를 알 필요가 있는데요..
저도 MS Jet엔진에 대해서는 잘 모르지만..
그것은 바로 엑셀에서 데이터가 들어 있는 영역(변수명 제외)의 최초의 8개 row를 보고 과반수로 의사결정하는 겁니다.
제가 Jet엔진에 대해서 잘 안다면 논란의 소지없이 정확히 설명드리겠지만... 거기까지는 저의 한계를 넘고, 대신 여러 형태로 엑셀데이터를 저장해보면서 테스트한 결과를 말씀드리면 다음과 같습니다.
1) 8개 row 중, Numeric이 과반수 이상이면, 숫자변수로 판단합니다. 예를들어 Numeric과 String이 5개, 3개이면 당근 숫자이고요. 똑같이 4개인 경우도 숫자변수입니다.
2) 아무것도 들어있지 않은 빈 Cell은 기권처리(^^)합니다. 따라서 Numeric 2개, String 2개, 빈셀 4개 인 경우 숫자입니다.
3) 8개 Row 전체가 비어 있는 경우는 엑셀의 해당 셀서식의 우열을 가지고 판단합니다.
즉, 8개의 Cell 포맷 중에 숫자서식이 많으면 숫자변수입니다. 그렇지만 숫자와 문자서식이 동수이면 이번에는 문자로 취급합니다.
여기서 엑셀의 Default 서식인 "일반"서식은 문자서식으로 취급합니다. 따라서 8개 row가 다 비었으면 웬만하면 문자로 잡힙니다.
참고로 통상적으로 SAS에서 숫자변수의 Missing으로 처리하는 "."에 대해서 Jet엔진은 무식하게도(^^) 문자로 봅니다.
따라서 SAS에서 사용하기 위해 엑셀에 데이터를 입력하실 때, 숫자변수의 미싱 값은 "."을 찍지 마시고 그냥 비워놓으세요. 잘못하면 엉뚱하게 문자로 인식될 우려가 있으니까요..
또, 문자냐 숫자냐의 판단기준이 되는 "8개의 row" 에서 "8"이라는 숫자는 사용자가 바꿀 수도 있는데요..
그것은, 레지스트리의 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 에서, TypeGuessRow 항목이 가지는 값을 원하시는대로 바꾸시면 됩니다.
(그렇지만 System에 대해 잘 아시는 분이 아니시라면 수정하는 것은 별로 권해드리고 싶지는 않습니다. 확인 정도만.. ^^; 엉뚱한 것을 손댓다가는 시스템이 문제를 야기할 수가 있으니까요.)
4.
Jet엔진을 통해 SAS는 변수명, 숫자/문자 구분과 함께 데이터값을 넘겨 받는데... 그 다음부터 조금 어려운 문제가 있습니다.
바로 변수의 길이와 관련된 문제인데요...
복잡하게 변수길이는 왜 따지느냐. 그냥 읽어서 SAS데이터로 저장하면 되지 않느냐! 하는 의문을 가지실 수도 있는데... 그 이유는 SAS 데이터의 구조상 반드시 필요하기 때문입니다.(라이브러리에서 데이터셋에서 view columns 해 보시면 길이를 확인하실 수 있습니다.)
즉, 변수의 길이는 다른 일반적인 랭귀지에서와 마찬가지로 메모리에 기록하고 계산을 하기 위해 반드시 필요한 사항이고, DB기능을 가진 데이터파일이라면 읽고 쓸 때의 효율성 면에서도 반드시 결정되어야 하는 것입니다.
일반적인 경우로 보더라도 데이터 구조상 "길이"에 대한 정보가 없는 데이터 형태는 순차적 접근(sequential access) 밖에 할 수 없기 때문에 워드같은 부정형의 일반적인 문서에 적합한 형태이고, SAS나 기타 DB의 기능을 조금이라도 가진 프로그램에서 사용하는 데이터셋/테이블들은 직접 접근(random/index access)과 프로그램 내부에서의 공간 할당을 위해 길이정보를 함께 포함하고 있습니다.
암튼, 엑셀데이터를 가져옴에 있어서.. 숫자변수의 경우에는 SAS가 기본적으로 8바이트의 길이를 할당하고 웬만한 숫자는 다 8바이트를 이용하여 표현이 가능하기 때문에 두 프로그램 사이에 문제될 소지가 크게 없습니다만, 문자변수의 경우는 한 글자가 1바이트를 차지하기 때문에 문자변수의 길이가 변수값들 중에 가장 긴 문자의 길이보다 크지 않으면 문자를 제대로 담지 못하게 되어 정보가 손실되는 문제가 발생하게 됩니다.
따라서 정보의 손실이 없을려면 처음부터 끝까지 데이터를 다 읽어본 다음에, 가장 긴 문자열을 찾아서 그것으로 문자변수의 길이를 정해야 하는데... 이렇게 되면 다시 난감해집니다. 왜냐하면 상당한 위험을 감수하고, Jet엔진을 통해 첫 8개 row를 이용해서 문자/숫자를 따져서 변수의 형을 결정하는 방법을 사용한 것이 아무 소용이 없어지게 되는 겁니다.
(참! 왜 굳이 이와 같은 상당한 위험을 감수하느냐...는 확실하지는 않지만, 속도문제가 가장 큰 것 같고, 데이터 스텝에서 문자변수의 길이를 정하는 방식도 비슷하기 때문인 것 같습니다. 이에 대해서는 바로 아래에 정리했으니 참고하세요.)
위와 같은 이유로 해서 SAS는 Jet엔진이 주는 문자/숫자정보와 마찬가지 개념으로 문자변수의 길이를 결정하는 방법을 그대로 쓰기로 하고 문자변수의 길이를 최초 8개 row의 길이 중 가장 큰 것을 채택하기로 한 겁니다.(상상입니다.^^)
(그러나 버전 8.1에서는 일괄적으로 255바이트를 주었었을 겁니다. 당시.. "야.. 이거 뭐 이래!" 했었고, 얼마 후 8.2를 설치한 다음 테스트해 보고, "흠. 제대로 고쳤군!" 했던 기억이 있거든요.)
-----------------------------
(*주) - SAS의 문자변수 길이 결정 방식
SAS는 문자변수의 길이를, 맨 처음 데이터스텝에서 데이터가 생성될 때, 해당 변수가 최초로 등호의 왼쪽에 위치할 때, 등호 오른쪽의 결과가 만들어 내는 문자의 길이로 결정하거든요
예를 들어...
data tt;
a="a";b=a||"b";output;
a="aaaa";b="abcd";output;
run;
proc print;run;
을 실행해 보시면, 다음과 같은 결과를 얻으실텐데요.
Obs a b
1 a ab
2 a ab
위와 같은 결과가 나타나게 된 이유는,
데이터스텝의 두번째 줄인 a="a";b=a||"b";output; 에서 a라는 문자변수가 등호의 오른쪽에 최초로 등장하므로, a="a"에 의해 a의 길이가 1로 결정되고,
b의 길이도 마찬가지로 a 다음의 b=a||"b" 에 의해 a의 길이인 1에 "b"의 길이 1이 더해져서 2로 결정되었기 때문에,
이하의 문장에서 a와 b에 아무리 긴 문자열을 주려고 해도 받을 수가 없었기 때문입니다. 데이터셋의 컬럼정보를 확인해 보시면 분명히 아실 수 있을 겁니다.
(input을 통해 읽어들이는 경우는, informat 등으로 길이가 정해지면 당연히 그 길이를 가지게 되고, 그렇지 않은 경우에는 길이가 8로 정해집니다.)
-----------------------------
5.
그런데 이러한 결정이 문제를 야기시켰습니다.
바로 한국/일본/중국처럼 2바이트 문자체계를 사용하는 나라에서...
이를 자세히 설명드리면..
엑셀을 보면, 문자의 길이를 나타내는 함수가 두 개 있는데... 하나가 len() 이고 다른 하나가 lenb() 함수입니다.
=len("가나다") 하시면 "3" 이 나올 겁니다. 그러나 =lenb("가나다")하시면 "6" 이 나옵니다.
=len("123가")하시면 4가 나오는데.. =len("123가")하시면, 5가 나옵니다.
즉, len함수는 한글 1글자의 길이를 1로 취급하는데.. lenb함수는 문자의 길이를 바이트단위로 셉니다.(한글 한 글자가 2바이트라는 것은 다 아시지요?)
그런데.. SAS가 이용하는 Jet엔진의 정보는 len()함수의 계산방식을 따라 변수의 길이를 제공하는 것 같습니다.(이것도 상상 ^^;)
SAS 8.2 는 이 정보를 곧이 곧대로 믿고 변수명의 길이를 채택했었는데... 아뿔사!ㅠㅠ
"가나다" 의 변수길이가 3으로 설정되게 되어버린 거지요. SAS는 기본적으로 바이트단위로 변수의 길이를 사용하니까요.
그 결과 "가"와 "나"의 반쪽만이 SAS 데이터셋으로 들어가게 된 겁니다.
모든 변수값이 영문으로만 되어 있는 미국에서는, 이런 문제는 발생하지 않을 것이므로 디버깅과정에서 걸러지지 않았던 것 같습니다.
6.
암튼.. SAS는 이러한 문제점를 발견하고... 패치프로그램을 통해 다시 8.1의 시대로 돌아갔습니다. 즉, 모든 문자변수의 길이를 255바이트로 주는 겁니다.
proc import를 이용해서 데이터를 읽어들여서 작업을 하시는 분은,
http://support.sas.com/techsup/unotes/SN/006/006709.html
을 참조하시고, proc import의 버그를 수정해주는
http://ftp.sas.com/techsup/download/hotfix/DBCS/v82/oledb/82ol05/win/82ol05wn.exe
을 다운받으셔서 설치하시는 것이 좋을 듯합니다.
하지만, 이것도 문자의 길이가 긴 데이터에서는 완벽한 해결책이 되지 못하는데요... 여전히 다음의 문제점을 안고 있더군요..
(1) len함수로 따졌을 때에는 256이내이지만, lenb함수로 따졌을 때에는 256이상인 경우에도 여전히 255로 잡혀서 문자가 짤립니다..
(2) 최초 8개 row에서 len함수로 따졌을 때 256이상인 문자는 Jet엔진이 문자/숫자 형을 판단하는데 기권처리됩니다. 즉, 예를들어 최초 8개 row에 numeric이 1개이고 나머지가 모두 string인데 이들 문자들의 길이가 모두 len으로 따져 256이상이라면.. 숫자 하나에 기권이 7이므로 그 변수는 숫자로 인식된다는 것입니다. ^^; Jet엔진의 버그 같은데.. 확실치는 않네요.
한편, 8번째 row 이내에 len으로 따져 256보다 큰 문자가 들어있고 우열을 따져 해당 column이 문자변수로 인식했다면, SAS는 묘한 행동을 합니다. ^^;
즉, "긴장모드! 뭔가 큰 놈이다!" 하고, 지금까지의 원칙을 깨고 처음부터 끝까지 엑셀을 다 읽고난 다음 변수의 길이를 결정하더군요.
전체관측치에서 가장 긴문자의 len함수값 곱하기 2로..
왜 곱하기 2를 했을까는, 추측하시겠지만, 실제 길이인 lenb()를 쓸 수는 없기 때문에 최악의 상황, 즉 모두 2바이트문자인 것으로 가정을 하고 그만큼 크게 잡아주는 것 같습니다.(이때문에 위에서 Jet엔진에서 lenb가 아닌 len함수로 계산한 변수의 길이를 제공한다고 상상한 것입니다. ^^)
또 전수조사를 해서 변수의 길이를 따질 때, 해당 문자변수 뿐 만이 아니고 같이 들어있는 다른 문자변수들까지 다 조사해서 각 변수의 길이를 각각의 최대 len값 곱하기 2로 변수의 길이를 결정해줍니다. 즉, 8 row 이내에 256이상의 문자가 들어있는 변수 덕분에 조금 시간은 걸리지만, 다른 문자변수들까지도 무조건 255바이트가 아닌, 적절하게 변수의 길이가 결정되는 겁니다.
결국... 문자를 읽어오는데 한글이 포함된 문자가 잘리는 경우가 발생하면.. 반드시 패치를 하셔야 하고요..
길이가 256글자를 넘는 경우 관측치가 포함되는 경우에는 해당 관측치를 최초의 8 row 이내에 배치하셔서 SAS로 하여금 전수조사를 하도록 유도하셔야 합니다.
그리고 패치 후 엑셀에서 읽은 문자변수의 길이가 모두 255바이트로 되어서 너무 불편하다고 판단되시면...
흠... 할 수 없습니다. 일일이 해당 변수의 최대길이를 조사하신 후, 그 길이를 가지는 새로운 변수를 정의하신 다음, 데이터를 옮기셔야 합니다. ㅠㅠ
그리고, 만약에 문자 변수간의 붙이는 연산(concaternation)이 필요할 때, 변수의 길이 중 값이 들어있지 않은 부분은 모두 공란으로 인식되기 때문에 compress함수등으로 공간을 없애셔야 원하시는 값을 얻으실 수 있을 겁니다.
7.
다음으로는 사용자의 실수로부터 야기되는 문제입니다.
엑셀파일을 열어보면, 멀쩡하게 숫자 값이 들어 있는데도 미싱(.)으로 나오는 경우와,
코드성격을 가진 변수로서 분명히 문자형식으로 입력을 했고 대부분의 값들은 다 문자형식으로 들어오고 있는데 일부의 값들이 공백으로 나오는 경우를 많이 경험하게 되는데요..
이렇게 된 이유는 위에서 말씀드린 것처럼 문자나 숫자변수의 유형이 바뀐 것이 아니라면,
눈에는 숫자로 보이지만, 실제로 그 값은 문자이기 때문이고(예를들어 어포스트로피가 앞에 붙은 숫자 또는 텍스트포맷하에서 입력된 숫자),
반대로 눈에는 다른 문자코드와 같이 보이지만 실제로는 숫자이기 때문입니다. (SAS에서 숫자에 대한 미싱은 "."으로 표현되지만, 문자의 미싱은 그냥 공백입니다)
즉, 엑셀에서 다른 값들과 문제 셀의 서식이 다르게 입력되었기 때문입니다.
이런 경우 사후적으로 엑셀의 셀서식을 바꾸어봐야 값 자체를 바꾸는 것이 아니므로 소용이 없고, 실제 해당 값을 문자에서 숫자로, 또는 숫자에서 문자로 바꾸어 주셔야 합니다...
(따라서 엑셀에서 데이터를 입력하실 때 실수를 줄이기 위해서는 사전에 컬럼 전체를 블럭으로 잡고, 원하시는 서식으로 잡아놓으신 다음에 입력하시는 것이 좋습니다)
그 방법으로는 (엑셀의 기능 중 제가 알고 있는 범위내에서 말씀드리자면) 다음의 몇가지 방법이 있습니다.
(1) 데이터 - 텍스트 나누기 : 엑셀 메뉴의 데이터-텍스트 나누기에서 서식을 정해주는 기능을 사용하면 됩니다. 이 방법은 한번에 한 컬럼씩 밖에 처리할 수 없는 단점이 있지만, 마법사를 통해 간편하게 사용할 수 있고 확실한 결과를 보장합니다.
변환을 원하시는 컬럼(영역)을 선택하신 다음, 데이터 - 텍스트나누기 클릭 후, 마법사에서 1,2단계는 적당히 선택(단, 실제 텍스트를 몇개의 컬럼으로 나누려는 의도로 하는 것이 아니므로 필드가 분할되지 않도록 해야 합니다)하시고, 3단계의 "열데이터 서식"에서 원하시는 숫자/문자서식을 지정해 주시면 됩니다.
(2) 복사 - 선택하여 붙여넣기 : 이 방법은 문자열로 저장된 숫자를 실제 숫자값으로 만들어 주는 경우에 매우 유용합니다. - 적극 추천... ^^;
아무 셀이나 값이 들어있지 않은 빈 셀을 "복사"하신 후, 문자 값에서 실제숫자로 변환해야하는 셀들이 들어 있는 영역을 블럭으로 잡은 다음, 편집메뉴의 "선택하여붙여넣기"(또는 오른쪽마우스클릭 후 나오는 "선택하여 붙여넣기")클릭 후, 팝업창의 "연산"의 "더하기"를 선택한 다음, "확인"하시면 됩니다.
참고로 엑셀의 도움말에서, "텍스트로 저장된 숫자를 숫자로 변환"을 검색하면 위에서 말씀드린 것과 비슷하지만, 1을 곱하는 방법을 이용하여 숫자로 변환하는 과정을 예로 보여주고 있는데, 이 방법에 의한 결과는 0과 미싱을 구별하지 못하는 문제점을 가지게 됩니다. 주의하세요.
(3) 함수이용 - 숫자나 문자열을 Value()함수, Text()함수 등을 이용하여 새로운 영역에 변환한 후, 복사-선택하여 붙여넣기(값 복사)하면 됩니다. 이 경우 위에서 1을 곱할 때와 마찬가지로 value()함수는 미싱과 0에 혼란이 없는 범위 내에서 사용할 수 있습니다.
8.
마지막으로 날짜형식에 대한 것인데요..
엑셀과 SAS는 특점시점을 시작점으로 잡아 일련번호로 부여하는 방식으로 날짜를 관리하고 있습니다.
그러나 기준이 되는 "특정시점"은 서로 다릅니다.
EXCEL에서는 1900년 1월 1일을 기준으로 잡고 1부터 시작합니다. 따져보니까 2000년 1월 1일은 36526 이 되고, 오늘(2004년 7월 1일) 은 38169가 되네요.
(아시겠지만, 셀서식에서 정해지는 표현 방식에 따라 이것이 2007-07-01도 되고 01Jul04도 되고 38169도 되는 것이지요.)
그리고 시간은 하루(=1)를 86400초로 나누어 소숫점자리를 사용하여 시간을 표시합니다. 즉 2004년 7월 1일 01시 39분 31초는 엑셀에서는 38169.06911로 표시되는데, 2004년 7월 1일이 38169이고, 01시 39분 31초는 01*60*60 + 39*60 + 31 = 5971 초이므로 이를 86400으로 나누면 0.06911 을 얻게 되는데 결국 둘을 더해 38169.06911이 되는 것입니다.
반면 SAS에서는 1960년 1월 1일을 0으로 시작해서 날짜를 카운트합니다.
그런데 엑셀과는 달리 날짜를 따지는데 두가지 방식을 씁니다.
하나는 엑셀과 마찬가지로 하루에 1씩 증가시켜서 계산하는 것과 다른 하나는 초단위로 세서 하루에 86400 (= 24 * 60 * 60) 씩 증가하는 것이지요.
따라서 하루를 1로 따지면 2004년 7월 1일은 16253 이고 86400초로 따졌을 때 2004년 7월 1일 01시 39분 31초는 1404265171 입니다.
SAS는 이 두 방식을 서로 엄격히 분리시켜 관리하기 때문에, 예를들어 put d date9.; 과 put d datetime20.; 은 같은 d라 할지라도 전혀 다른 날짜를 출력합니다.
그리고 이 두가지 형식의 관리체계는 DATEPART(), TIMEPART(), DHMS() 등의 함수를 통해 호환됩니다.
암튼..
이렇게 시작시점이 다르고 관리방식에 차이가 있기 때문에 두 프로그램 상에 데이터 호환의 문제가 발생할 여지가 있습니다.
날짜서식만 정확히 지켜진다면, 즉 엑셀에서 셀서식이 날짜형식을 가지고 있거나, SAS에서 데이터가 날짜 format으로 저장되어 있다면
SAS가 import할 때와 export할 때 적절히 변환시켜주기 때문에 날짜 자체에는 문제 없습니다만,
import할 때 SAS의 엄격히 2원화된 날짜관리방식때문에 사용자의 오해를 자주 일으킵니다.
즉, 엑셀에서 날짜서식을 가지고 있는 데이터는 SAS가 date informat이 아니라 datetime informat으로 즉, 86400초단위로 되어 있는 날짜체계로 변환해서 읽어들여 온다는 것입니다.
따라서 SAS에서 출력할 때, 일반적으로 사용하는 date format으로 출력하면 위에서 말씀드린 것과 마찬가지로 엉뚱한 날짜가 출력되는 것입니다.
이럴 때에는 viewtable을 통해 읽어들여온 데이터셑이 과연 어떠한 포맷을 가지고 있는지를 확인하시고, 초단위까지 값을 가진다면 datetime format을 이용하시거나 위에서 말씀드린 함수를 사용하시면 해결될 겁니다. 참고로 datetime 포맷은 SAS 도움말에서 "formats" 또는 "informats" 색인을 이용해서 찾아보실 수 있습니다.
--------
지금까지 장황한 설명이지만 "SAS에서 엑셀데이터를 읽어오는데 왜 이렇게 문제가 많나!" 하는데 대해서는 대충 이해를 하셨을 것 같고요...
SAS가 가진 한계점도 있지만.. 어느정도는 데이터 형식의 차이에서 오는 불가피한 것이다라는 점을 이해해주시면 될 것 같습니다.(저.. SAS회사하고는 관련없습니다! ^^)
제가 확실히 알지도 못하고 너무 장황하게 쓴 것 같습니다. 감안해서 보셔야 할 겁니다.
잘못된 정보를 발견하시면 말씀해 주십시요. 즉시 잘못을 바로 잡겠습니다.
다음검색