SAS에서 excel 파일 불러오기 (PROC IMPORT)

1) proc import

PROC IMPORT OUT=raw.data1           DATAFILE="C:\Users\UOS\Desktop\excel\101_DT_1K52B01_20140707170219_2.xlsx"
            DBMS=  XLSX   REPLACE;
            SHEET="sheet1";

           range="Sheet1$E7:K17" ;

statements for importing from delimited files

DATAROW=n;         *** 어느 열부터 읽기시작 ********

DELIMITER=char | 'nn'x;

GETNAMES=YES | NO; ** variable name 유무 default는 yes

GUESSINGROWS=n | MAX;

*변수명 바꾸기*/
%macro varname_change(data);
data &data;
 set &data;

 rename VAR1 = rigion
   VAR2 = industry
   VAR3 = biz
   VAR4 = num_biz
   VAR5 = num_emp;

 label VAR1 = '행정구역별'
   VAR2 = '산업별'
   VAR3 = '사업체구분별'
   VAR4 = '사업체수'
   VAR5 = '종사자수';
run;
%mend;

%varname_change(raw.data1);
%varname_change(raw.data2);
%varname_change(raw.data3);
%varname_change(raw.data4);
%varname_change(raw.data5);

 

/*필요없는 변수 제거*/
%macro var_drop(data);
data &data;
 set &data;
 drop biz;
run;
%mend;
%var_drop(raw.data1);
%var_drop(raw.data2);
%var_drop(raw.data3);
%var_drop(raw.data4);
%var_drop(raw.data5);

 

Available Statements for Importing and Exporting Excel Files Using DBMS=XLS and DBMS=XLSX
DBMS= Identifier	Option	Valid Value	Default Value	PROC	PROC
				IMPORT	EXPORT
XLS	ENDCOL	Last column for data	Last column that contains data	Yes	No
	ENDNAMEROW	Last row for variable names	Same as NAMEROW	Yes	No
	ENDROW	Last row for data	Last row that contains data	Yes	No
	GETNAMES	Yes | No	Yes	Yes	No
	NAMEROW	First row for variable names	First row that contains variable names	Yes	No
	NEWFILE	Yes | No	No	No	Yes
	PUTNAMES	Yes | No	Yes	No	Yes
	RANGE	name | sheet$ul:lr	First row	Yes	No
	SHEET	Sheet name	First sheet	Yes	Yes
	STARTCOL	First column for data	Last column that contains data	Yes	No
	STARTROW	First row for data	First row that contains data	Yes	No
XLSX	GETNAMES	Yes | No	Yes	Yes	No
	RANGE	name | sheet$ul:lr	First row	Yes	No
	SHEET	Sheet name	First sheet	Yes	Yes

2) SAS LIBNAME Statement for EXCEL and ACCESS Engine 

     (sas 9.4부터 가능)

 LIBNAME <libref> engine-name <'physical-path and filename.ext'> 

<SAS/ACCESS engine-connection-options> 
<SAS/ACCESS LIBNAME-options>;

/* because Excel field names often have spaces */
options validvarname=any;
 
libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';
 
/* discover member (DATA) names */
proc datasets lib=xl; quit;
 
libname xl CLEAR;

만약 excel에서 특정data sheet (sheet1)를 불러오고자 할떄는

data xxx ; set xl.sheet1 ;

run ;