exchange_partition_by_list

/*
Important: Please consider this code as a benchmark. It perfectly works in my machine for my purposes. But maybe has to be updated for your needs. Thanks. Do not hesitate to contact me.
_ORALIB - oracle library;
_ora_table_name - partitioned oracle output table name
_ora_table_for_exch_name - table for partition exchange
_authdomain - authdomain
_orapath - orapath
_data_exch - table with data to be appended to  table for partition exchange
_sqlldr_path - specify path to sqlldr client
_table - table for generating partition name and partition value 
part_name - partition name
part_value - partitin value


Author: Wiktor Byrda
email:  viktar.byrda@gmail.com
*/

/* Example of a table creation partitioned by list */
proc sql;
 %if %sysfunc(exist( &_ORALIB..&_ora_table_name. )) %then %do;
	DROP TABLE  &_ORALIB..&_ora_table_name. ;
 %end;
   connect to oracle (authdomain=%str(%'&_AUTHDOMAIN.%') path=%str(%'&_ORApath.%'));
	execute(
create table &_ora_table_name.

(POPULATION NUMBER,
COUNTRY VARCHAR2(30))
 partition by list(COUNTRY)
 ( partition P_POLAND values ('POLAND')
)
)
by oracle;
disconnect from oracle;
quit;

/* create table for exchange - has to be exact copy of oracle table*/
%MACRO create_table_for_exchange(_oralib=, _ora_table_name=, _ora_table_for_exch_name=, _authdomain=, _orapath=);

proc sql;
 %if %sysfunc(exist(  &_ORALIB..&_ora_table_for_exch_name. )) %then %do;
	DROP TABLE   &_ORALIB..&_ora_table_for_exch_name. ;
 %end;
connect to oracle (authdomain=%str(%'&_AUTHDOMAIN.%') path=%str(%'&_ORApath.%'));
execute(
create table &_ora_table_for_exch_name.
for exchange with table &_ora_table_name.
)
by oracle;
disconnect from oracle;
quit;
%MEND;

/* appending table for exchange partition */
%MACRO append_table_to_exchange(_oralib=, _data_exch=, _ora_table_for_exch_name= );
proc append base=&_ORALIB..&_ora_table_for_exch_name.
(
BULKLOAD=YES
BL_OPTIONS='PARALLEL=TRUE'
BL_SQLLDR_PATH='&_sqlldr_path.'
)
data=&_data_exch.;
run;
%mend;

/* ADD PARTITION IF NOT EXIST AND EXCHANGE*/
%MACRO exchange_part(_ora_table_name= ,_ora_table_for_exch_name= ,_AUTHDOMAIN = ,_ORApath = );
proc sql;
   connect to oracle (authdomain=%str(%'&_AUTHDOMAIN.%') path=%str(%'&_ORApath.%'));
	execute(
declare v_partition_count number;
table_name string(30);
begin
select %str(%'&_ora_table_name.%') into table_name from dual;
select count(*) into v_partition_count  from user_tab_partitions 
where table_name = %str(%'&_ora_table_name.%')
and partition_name = %str(%'&part_name.%');

if v_partition_count = 0 then
execute immediate 'alter table ' ||table_name|| '  ADD PARTITION '||' '||%str(%'&part_name.%')|| ' ' ||' values ('%str(%'&part_value.%')')';

end if;
end;
)
by oracle;
disconnect from oracle;
quit;

/* exchange partition */
proc sql;
connect to oracle (authdomain=%str(%'&_AUTHDOMAIN.%') path=%str(%'&_ORApath.%'));
execute (alter table &_ora_table_name.
exchange partition &part_name.
with table &_ora_table_for_exch_name.) by oracle;
disconnect from oracle;
quit;
%MEND;


/* TEST DATA */
/* creating table with data for partitions */
data TEST;
LENGTH COUNTRY $30;
input COUNTRY $ 1-9 POPULATION;
datalines;
GERMANY   83200000
GERMANY   83200000
GERMANY   83200000
GERMANY   83200000
GERMANY   83200000
FRANCE    68000000
SERBIA    6834000
SLOVAKIA  5447000
BELGIUM   11590000
SLOVENIA  2108000
;
RUN;

/* Macro for loading data into partitioned table */
%macro load_partitions;

PROC SQL NOPRINT;

SELECT DISTINCT COUNTRY INTO: VAR_LIST SEPARATED BY ' ' 
FROM TEST 
ORDER BY COUNTRY; 
%PUT &=VAR_LIST;
QUIT;

%do i=1 %to %sysfunc(countw(&VAR_LIST));
%let next_country = %scan(&VAR_LIST, &i);

DATA TEST2;
SET TEST;
IF COUNTRY = "&next_country.";
RUN;

data _null_;
set test2;
call symputx('part_name', cats("P","_", COUNTRY));
call symputx('part_value', COUNTRY);
run;
%PUT &=PART_NAME &=PART_VALUE;
%create_table_for_exchange
(
 _oralib=
,_ora_table_name=
,_ora_table_for_exch_name=
,_authdomain=
,_orapath=
);

%append_table_to_exchange
(
 _oralib=
,_ora_table_for_exch_name=
,_data_exch = test2
)
;

%exchange_part
(
 _ora_table_name = 
,_ora_table_for_exch_name =
,_AUTHDOMAIN = 
,_ORApath = 
);

%END;
%MEND;

%load_partitions;