[SAS 고수의 팁] 효율적인 Lookup Tables

[#1]

Lookup Table 이란, 주어진 연산에 대해 미리 계산된 결과들의 집합(배열)을 가리킵니다. 이 집합(배열)은 주어진 연산에 대한 결과를 계산하는 시간보다 더 빠르게 값을 취득해 갈 수 있도록 사용되는 레퍼런스로 사용됩니다.

더 자세히 설명하면, Lookup Table에 저장이 되어 있는 'Key' 값들은 다른 테이블에 의해 사용이 되는데,

예를 들어서 소비자 (Customer) 테이블과, 거래처 (Vendor) 테이블에서 동시에 나라(Country) 테이블 참조하는 것입니다.

Lookup table의 장점은 아래와 같습니다.

- 데이터가 차지하는 용량을 줄일 수 있다 .

- Lookup을 사용함으로써 메모리를 읽는 용량과 처리량은 작아진다. 이로 인해서 IO, CPU, 메모리의 사용량이 작아진다.

- 데이터 집중화: 모든 테으블과 칼럼은 특정 '값'을 참조합니다. 모든 테이블이 '미국'이라는 내용을 Country Column에 넣는다고 했을 때, 일관되게 'US' 로 표시를 한다는 것이다.

- 데이터 수정의 유연성: 만약 Lookup Table이 존재하지 않는 상태에서, 'US(Key)-Value(United States)'로 표시가 된 내용을, 모두 'US(Key)-Values(United States of America)'로 표시하는 것으로 수정하려면, 많은 연산이 필요하다. 그러나, Lookup Table이 있다면 한 줄만 수정하면 된다.

이번 게시글은 데이터 세트를 룩업 테이블과 병합하는 간단한 방법을 사용하여 그 이유에 대해 설명합니다.

 

사용 데이터 셋.

Data Sets 1.

▶MAIN : 428 Observations and 15 variables

%LET mult = 1; /* 10, 100, 1000, 2000, 5000 */
DATA main;
SET sashelp.cars;
DO i = 1 TO &mult.;
OUTPUT;
END;
RUN;

Data Sets 2.

▶ LOOKUP_ORIGIN : COL 3, VARIABLE 3.

PROC SQL;
CREATE TABLE lookup_origin AS
SELECT origin /* key */
,COUNT(DISTINCT make) AS make_n
,COUNT(DISTINCT type) AS type_n
FROM sashelp.cars
GROUP BY
origin
ORDER BY
origin
;
QUIT;

Data Sets 3.

▶LOOKUP_TYPE : 15 observations and 6 variables

PROC SQL;
CREATE TABLE lookup_type AS
SELECT origin, type /* keys */
,COUNT(DISTINCT model) AS type_model_n
,COUNT(DISTINCT make) AS type_make_n
,MEAN(msrp) AS type_msrp_mean
,MAX(horsepower) AS type_horsepower_max
FROM sashelp.cars
GROUP BY
origin, type
ORDER BY
origin, type
;
QUIT;

Data Sets 4.

▶LOOKUP_MAKR : 38 observations and 6 variables

PROC SQL;
CREATE TABLE lookup_make AS
SELECT origin, make /* keys */
,COUNT(DISTINCT model) AS make_model_n
,COUNT(DISTINCT type) AS make_type_n
,MEAN(msrp) AS make_msrp_mean
,MAX(horsepower) AS make_horsepower_max
FROM sashelp.cars
GROUP BY
origin, make
ORDER BY
origin, make
;
QUIT;

 

DATA Step Merge.

​

가장 기본적인 Merge 방법입니다. 각 조인에 대해 두 데이터 세트를 같은 방식으로 정렬해야 하기 때문에 효율성이 가장 낮은 방법 중 하나입니다. Lookup 데이터 세트가 작을수록 정렬이 빠를지라도 큰 마스터 데이터 세트는 매번 재정렬해야 합니다.

PROC SORT DATA = main OUT = datastepmerge1; BY origin make; RUN;
DATA datastepmerge2;
MERGE datastepmerge1 lookup_origin;
BY origin;
RUN;

DATA datastepmerge3;
MERGE datastepmerge2 lookup_make;
BY origin make;
IF msrp > make_msrp_mean THEN make_msrp_flag = 1;
ELSE make_msrp_flag = 0;
make_horsepower_pct = 100 * horsepower / make_horsepower_max;
RUN;

PROC SORT DATA = datastepmerge3 OUT = datastepmerge4; BY origin type; RUN;
DATA datastepmerge5;
MERGE datastepmerge4 lookup_type;
BY origin type;
IF msrp > type_msrp_mean THEN type_msrp_flag = 1;
ELSE type_msrp_flag = 0;
type_horsepower_pct = 100 * horsepower / type_horsepower_max;
RUN;

SQL Join

PROC SQL은 데이터가 증가할수록 효율성이 떨어지지만 데이터 볼륨이 적지만 데이터 볼륨이 많은 금융 데이터와 함께 널리 사용되는 임상 데이터에는 거의 사용되지 않습니다.

PROC SQL;
CREATE TABLE sqljoin1 AS

조회 테이블의 새 변수와 함께 데이터 세트(기본)의 모든 변수를 복사합니다.

(b=lookup_origin, c=lookup_make 및 d=lookup_type)

SELECT a.*
,b.make_n
,b.type_n
,c.make_model_n
,c.make_type_n
,c.make_msrp_mean
,c.make_horsepower_max
,d.type_model_n
,d.type_make_n
,d.type_msrp_mean
,d.type_horsepower_max

SQL 표현식 구문을 사용하여 백분율을 계산합니다.

,(100 * a.horsepower / c.make_horsepower_max) AS make_horsepower_pct
,(100 * a.horsepower / d.type_horsepower_max) AS type_horsepower_pct

CASE 구문은 DATA 단계 IF 문에 해당하는 SQL입니다.

,(CASE
WHEN a.msrp > d.type_msrp_mean THEN 1
ELSE 0
END) AS type_msrp_flag
FROM main a

조회 테이블은 LEFT JOIN을 사용하여 main과 병합됩니다.

LEFT JOIN
lookup_origin b
ON a.origin = b.origin
LEFT JOIN
lookup_make c
ON a.origin = c.origin AND a.make = c.make
LEFT JOIN
lookup_type d
ON a.origin = d.origin AND a.type = d.type
;
QUIT;

Generated SAS Formats

SAS 형식을 사용하는 것이 데이터 세트를 직접 결합하는 것보다 더 효율적입니다. 포맷 데이터는 디스크가 아닌 메모리에 저장되기 때문입니다. 아래의 데이터 세트는 상대적으로 작기 때문에 SAS 형식을 조회 테이블로 사용하면 상당한 장점이 있습니다.

DATA format_origin;
LENGTH fmtname $7 start $80 label 8 type hlo $1;
SET lookup_origin;
type = 'I';
hlo = ' ';
start = origin;
fmtname = 'originm';
label = make_n;
output;
fmtname = 'origint';
label = type_n;
output;
RUN;
PROC SORT DATA = format_origin NODUPKEY; BY fmtname start; RUN;
PROC FORMAT CNTLIN = format_origin; RUN;

CNTLIN 데이터 세트에 있는 동일한 이름의 필수 변수와 TYPE 간의 충돌을 방지하려면 level1= 변수의 이름을 변경해야 합니다.

%MACRO generate_format(level1=, level2=);
DATA format_&level1.;
LENGTH fmtname $7 start $80 label 8 type hlo $1;
SET lookup_&level1. (RENAME = (&level1.=level1));
type = 'I';
hlo = ' ';
start = CATX('|', origin, &level1.);
fmtname = "&level1.c";
label = &level1._model_n;
output;
fmtname = "&level1.x";
label = &level1._&level2._n;
output;
fmtname = "&level1.p";
label = &level1._msrp_mean;
output;
fmtname = "&level1.h";
label = &level1._horsepower_max;
output;
RUN;
PROC SORT DATA = format_&level1. NODUPKEY; BY fmtname start; RUN;
PROC FORMAT CNTLIN = format_&level1.; RUN;
%MEND generate_format;
%generate_format(level1=make, level2=type);
%generate_format(level1=type, level2=make);
DATA format1;
SET main;
make_n = INPUT(origin, originm.);
type_n = INPUT(origin, origint.);
make_model_n = INPUT(CATX('|', origin, make), makec.);
make_type_n = INPUT(CATX('|', origin, make), makex.);
make_msrp_mean = INPUT(CATX('|', origin, make), makep.);
IF msrp > make_msrp_mean THEN make_msrp_flag = 1;
ELSE make_msrp_flag = 0;
make_horsepower_max = INPUT(CATX('|', origin, make), makeh.);
make_horsepower_pct = 100 * horsepower / make_horsepower_max;
type_model_n = INPUT(CATX('|', origin, type), typec.);
type_make_n = INPUT(CATX('|', origin, type), typex.);
type_msrp_mean = INPUT(CATX('|', origin, type), typep.);
IF msrp > type_msrp_mean THEN type_msrp_flag = 1;
ELSE type_msrp_flag = 0;
type_horsepower_max = INPUT(CATX('|', origin, type), typeh.);
type_horsepower_pct = 100 * horsepower / type_horsepower_max;
RUN;

format_origin의 데이터는 다음과 같이 표시됩니다.