SAS 데이타셋을 오라클에 Import하기 위한 External Table DDL 스크립트 및 Import Data 파일 생성

/*
 Title  : SAS 데이타셋을 오라클 External Table을 이용하여 Import할 DDL 스크립트 소스와 SAS 데이타셋 Export 파일 생성
 Creator : grapro
*/
*** Parameter Setting ****************************************************************;
%let gextlib  = crs05;  ** 오라클에 업로드할 SAS테이블 라이브러리명 **;
%let gexttbl  = tb_01;  ** 오라클에 업로드할 SAS테이블명 **;
%let glogfile  = sam.log;  ** External Table Import시 log파일 **;
%let gdatafile  = ex.txt;  ** External Table Import 파일  **;
%let gsrcfile  = c:\source.txt; ** 오라클에서 실행할 External Table Import 소스코드 저장파일 **;
rsubmit;
%let gextlib  = crs05;  ** 오라클에 업로드할 SAS테이블 라이브러리명 **;
%let gexttbl  = tb_01;  ** 오라클에 업로드할 SAS테이블명 **;
%let gexfile  = ~/ex.txt;  ** External Table Import 데이타파일 위치  **;
endrsubmit;
**************************************************************************************;

%macro kk(extlib,exttbl,logfile,datafile,srcfile);

 proc sql;
  create table var_list as
  select  varnum,
   name,
   label, 
   type,
   length
  from dictionary.columns
  where  libname = upcase("&extlib") & upcase(memname) = upcase("&exttbl")
  ;
 quit;

 data _null_;
 set var_list end=end nobs=nobs;
         call symput ('varname'||left(put(_n_,4.)),name);
         call symput ('varlabel'||left(put(_n_,4.)),label);
         call symput ('vartype'||left(put(_n_,4.)),left(type));
         call symput ('varlen'||left(put(_n_,4.)),trim(left(length)));
        
         if _n_ = 1 then  call symput ('comma'||left(put(_n_,4.)),"");
         else   call symput ('comma'||left(put(_n_,4.)),",");
        
         if end then call symput ('nobs',nobs);
 run;
  
 
 data extddl;
  length x $500.;
  x = " CREATE TABLE t_&exttbl   "; output;
  x = " (                                     "; output;
  %do i=1 %to &nobs;
   %if  &&vartype&i = num %then %do;
    x = "    &&comma&i &&varname&i NUMBER(&&varlen&i.) "; output;
   %end;
   %else %do;
    x = "    &&comma&i &&varname&i VARCHAR2(&&varlen&i.) "; output;
   %end;
  %end;
  x = " )                                     "; output;
  x = " ORGANIZATION EXTERNAL                 "; output;
  x = " (                                     "; output;
  x = "     TYPE ORACLE_LOADER                "; output;
  x = "     DEFAULT DIRECTORY SAM_DIR         "; output;
  x = "     ACCESS PARAMETERS                 "; output;
  x = "     (                                 "; output;
  x = "         RECORDS DELIMITED BY NEWLINE  "; output;
  x = "         NOBADFILE                     "; output;
  x = "         LOGFILE '&logfile'             "; output;
  x = "         NODISCARDFILE                 "; output;
  x = "         FIELDS TERMINATED BY 0x'09'   "; output;
  x = "         (                             "; output;
  %do i=1 %to &nobs;
   x = "        &&comma&i &&varname&i "; output;
  %end;
  x = "         )                             "; output;
  x = "     )                                 "; output;
  x = "     LOCATION('&datafile')  "; output;
  x = " )                                     "; output;
  x = " REJECT LIMIT UNLIMITED;               "; output;
 run;
 
 

 %macro exportfile(exlib,extbl,exfile);
  proc sql;
   create table var_list as
   select  name
   from dictionary.columns
   where  libname = upcase("&exlib") & upcase(memname) = upcase("&extbl")
   ;
  quit;

  data _null_;
  set var_list end=end nobs=nobs;
          call symput ('varname'||left(put(_n_,4.)),name);
        
          if end then call symput ('nobs',nobs);
  run;
   

  data  _null_;
  set &exlib..&extbl;
  file  "&exfile" dsd dlm="09"x;
  put  
   %do i=1 %to &nobs;
    &&varname&i
   %end;
   ;
  run;
 %mend;
 %exportfile(work,extddl,&srcfile);

%mend;

 

 

 

rsubmit;
%macro exportfile(exlib,extbl,exfile);
 proc sql;
  create table var_list as
  select  name
  from dictionary.columns
  where  libname = upcase("&exlib") & upcase(memname) = upcase("&extbl")
  ;
 quit;

 data _null_;
 set var_list end=end nobs=nobs;
         call symput ('varname'||left(put(_n_,4.)),name);
       
         if end then call symput ('nobs',nobs);
 run;
  

 data  _null_;
 set &exlib..&extbl;
 file  "&exfile" dsd dlm="09"x;
 put  
  %do i=1 %to &nobs;
   &&varname&i
  %end;
  ;
 run;
%mend;
endrsubmit;

 

 

%kk(&gextlib,&gexttbl,&glogfile,&gdatafile,&gsrcfile);
rsubmit;%exportfile(&gextlib,&gexttbl,&gexfile);endrsubmit;