Sunday, July 29, 2012

More on Performing Principal Component Analysis in Oracle

Wiht Oracle UTL_NLA package, we can call routines to perform PCA on vectors and matrices represented as VARRAYs. How ever, vectors and matrices are stored in VARRAYs with a maximum size of one million entries. Given this restriction, UTL_NLA vectors can be up to one million entries but matrices need to be of size RxC <= 1,000,000.


That's why I have developed my own functions to perform PCA. It is much easier to use and can handle unlimited number of records. Data and operations always stay in the database.

Saturday, July 28, 2012

Perform Principal Component Analysis (PCA) Using R, SAS and SQL


Perform Principal Component Analysis (PCA) in R.

R1. Using princomp(m1): by default covariance matrix is used. data is center shifted but not scaled. princomp(m1)$score can be precisely replicated by:
scale(m1,scale=F)%*%princomp(m1)$loading
R2. princomp(d, corr=T): correlation matrix is used. data is center shifted and scaled based on standard deviation. However, standard deviation is based on divisor N not N-1. princomp(m1, cor=TRUE)$score can be precisely replicated by:
((m1-princomp(m1, cor=TRUE)$center)/princomp(m1, cor=TRUE)$scale)%*%princomp(m1, cor=TRUE)$loading

Perform PCA in SAS. By default, correlation matrix is used.
SAS 1. proc princomp data=M1 cov out=m1_pca;
         run;

SAS 2. proc princomp data=M1 out=m1_pca_cor;
         run;

Scores from R1 match scores from  SAS 1.
Scores from R2 roughly match scores from SAS 2. The difference is caused by that in R,  standard deviation, used as scaling facor, is based on divisor N not N-1. In SAS,   the divisor for standard deviation is N-1.

Calculating PCA in a database using SQL is a very interesting way. We can perform PCA  on large data sets.

Friday, July 20, 2012

SAS strip vs Oracle trim functions



SAS Strip and Oracle trim functions are equivalent. Both  can remove all leading and trailing blanks.  With Oracle trim, one can also define specific characters to be removed.



Oracle trim examples.

trim ('  hello ') returns 'hello'
trim (both '0' from '0012130')  returns 1213

SAS Strip remove leading and trailing blanks.

SAS data set vs. relational database table

There is a key difference between SAS data sets and relational database tables. In SAS data set, like a text file, there is a natural, fixed order of records (or observations in SAS term). The order of a data set will not change unless we specifically modify it using method like "proc sort". So in SAS, it makes perfect sense to say "extract the first 500 observations from a data set".

However, in a relational database table, there is no natural order defined for records in a table. To say "exact the first 500 records from a table" does not make sense. We have to specifically define an order. Thus in a relational database, the following statements all make sense.
"Extract 500 records randomly from a table."
"Extract the top 500 records with the highest account numbers from a table (assuming account numbers are unique)".

Caution needs to be taken when converting SAS data set to a relational data table. The natural order of records in the SAS data set will be lost. One way to preserve the natural order of a SAS data set is to  create an extra column based on SAS system variable _N_.


Tuesday, July 10, 2012

How to tokenize text in Oracle

The following procedure splits text column query into keywords,e.g., "Hello World" into two words "Hello" and "World".


Step 1. Create index of type ctxsys.contex.
create index t_query_idx on t_query(query) indextype is ctxsys.context;


Step 2.
set serveroutput on;
declare
the_tokens ctx_doc.token_tab;
begin
for i in (select row_p_key from t_query order by row_p_key) loop
ctx_doc.tokens('t_query_idx', i.row_p_key, the_tokens);
for ii in 1..the_tokens.count loop
insert into tbl_query_token select i.row_p_key,the_tokens(ii).offset, the_tokens(ii).token from dual;
end loop;
commit;
end loop;
end;
/

Sunday, June 17, 2012

How to securely mask credit card numbers

Often banks need to send large amount of data to outside vendors to perform data analysis. It is safer to mask sensitive data items such as credit card numbers, social security numbers, etc. The following is one approach to securely mask credit card numbers.

1. Generate a random number for each credit card number.
2. Generate a rank for each random number using row_number().
3. Generate the masked credit card number based on the rank.
Step 1. create table tbl_card_w_rand_num as select card_number, dbms_random.random() rnd from tbl_cards;
Step 2. Generate the masked credit card numbers based on the rank of random numbers. Keep this table in a safe place.

create table tbl_masked_card_num as with tbl as(
select a.*, row_number() over(order by rnd) rnk from tbl_card_w_rand_num a)
select a.*, 'A'||lpad( rnk,15,'0') masked_card_num from tbl a;
Step 3. Create data using masked_card_num

Wednesday, June 13, 2012

Calculate moving average: SAS vs Oracle


Oracle analytic windows functions provide a great way to calculate cross-row information such as moving average. The following examples show that using Oracle analytic function to calculate moving average is more compact, readable and less error-prone than using SAS scripts.

1. Calculating Moving Average using Oracle Analytic Function
create table ds2 as
select a.*, avg(num) over(partition by account_id order by month rows between 4 preceding and current row) mov_aver from ds1 a;

2. Calculating Moving Average using SAS
proc sort data=ds1;
by  account_id  month;
run;
%let n = 4;
data ds2;
set ds1;
by account_id;
retain num_sum 0;
if first.account_id then do;
count=0;
num_sum=0;
end;
count+1;
last&n=lag&n(num);
if count gt &n then num_sum=sum(num_sum,num,-last&n);
else num_sum=sum(num_sum,num);
if count ge &n then mov_aver=num_sum/&n;
else mov_aver=.;
run;

Sunday, June 10, 2012

Converting SAS scripts into Oracle PL/SQL. Why Bother?

I am converting large quantity of existing SAS scripts into Oracle. So far, all the SAS scripts can be implemented into Oracle PL/SQL (mostly SQL). Given the same input data, SAS and Oracle produce the exactly same result most of the time. In very limited number of cases, there are very small discrepancies in SAS and Oracle results. One of such example is calculating percentiles. However, these discrepancies are very small and explainable.

Why the conversion of existing SAS scripts into Oracle PL/SQL?  The following are the reasons.
1. Production data existing in Oracle DB all the time. It is better to run analytics processes within the database without data movement and enjoy all those benefits offered by DB, e.g., security, manageability, scalability, indexes, materialized views,table partitions, SQL, etc.
2. To democratize data analytics. Only limited number of people know how to use SAS( many of them have at least master degrees). However, there are far more SQL programmers/analysts who are querying data on daily basis. To us, data analytics is simply a more advanced way of querying data, nothing more, nothing less. It is natural to  take advantage of analytics power using SQL.

Tuesday, June 05, 2012

SAS proc rank and its Oracle SQL equivalent


The following SAS rank and Oracle SQL produce the same results. In SAS, when there are ties, the average rank is used. To do the same in Oracle, we calculate the unique row numbers and then its average based on group id and variable (amt).

SAS rank.
proc rank data=tbl_in  out=tbl_out ties=mean descending;
by grp;
var amt;
ranks amt_rnk;
run;

Oracle SQL rank.
create table tbl_out
as with tbl as
(
select a.*,
row_number() over(partition by grp order by  amt  desc)  amt_rnk0  from tbl_in a
) select a.*, (avg( amt_rnk0  ) over(partition by  grp,  amt )) amt_rnk
  from tbl a;

Saturday, June 02, 2012

Five Ways of Creating Unique Record Identifier For Oracle Tables

I have used the following five ways to create a unique record identifier for an Oracle table using SQL.
1. Simply use Oracle pseudocolumn rowid
Oracle pseudocolumn rowid comes with every table. It looks like the following.
SQL> select rowid from table_test where rownum <3;
ROWID
------------------
AAAYLPAAVAAEvLTAAA
AAAYLPAAVAAEvLTAAB
2. Create a  unique record id using rownum
create table table_test_new as select rownum as uniq_id, a.* from table_test a;
3. Create a unique record id using row_number()
create table table_test_new2 as select row_number() over(order by LOG_DATE)  uniq_id, a.* from  table_test a;
4. Create a unique record id using sequence.
create sequence rec_id_seq;
create table table_test_new3 as select rec_id_seq.nextval uniq_id, a.* from  table_test a;
5. Create a unique record id using sys_guid()
According to Oracle document, SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes.
create table table_test_new4 as select sys_guid() uniq_id, a.* from  table_test a;
What are other ways to create a unique record id that I am missing?

Thursday, May 31, 2012

Floating Point Representation: the Loss of Precision

On SAS PC, 11169568236203649 is actually represented as 11169568236203648 due to the limitation of  64-bit IEEE floating point representation. 


In most of the applications, there is practically no difference between  11169568236203649  and 11169568236203648. Unfortunately, every digit matters in the case of function mod. 

  

Wednesday, May 30, 2012

A Clarification on SAS mod function.

I am a long term SAS fan.  Its statistical functions and powerful macro scripting capability are simply unprecedented.  There is a little bit of misunderstanding concerning my post on MOD function.  The following is a clarification from Rick Langston from SAS.

Rick's clarification follows.
--------------------------------------------------------
There is no such "bug" in the MOD function in SAS.
The problem is due to the limitation of IEEE floating point
representation with the number of digits used in the example.
The number 11169568236203649 cannot be precisely stored in a 64-bit
IEEE floating point value. It is indistinguishable from
11169568236203648, for example.
SAS uses floating point representation for all of its numeric values.
Note that if you apply the same constraints to databases, you
will see the same results as SAS. For example, on Teradata
if you submit this expression:

select cast(11169568236203649 as float) mod cast(30269 as float);
You get the response of
        1.17310000000000E 004
Which is just what SAS provides.

And consider this C code:
main()
{
double x;
x = (long long)11169568236203649.0 % (long long)30269.0;
printf("x=%f.\n",x);
x = 11169568236203649LL % 30269LL;
printf("x=%f.\n",x);
}

The results are this:
x=11731.000000.
x=11732.000000.

It's the same issue. The double-precision value cannot
be represented sufficiently. If the value remains as a long
integer, the representation is maintained.

Note also that when you run the same MOD function example on
z/OS (i.e. IBM mainframe), you get 11732. This is because the
IBM floating point representation has 4 more bits of mantissa
(at the expense of 4 less bits of exponent) than does IEEE. So
more digits of precision will be noticed, as is the case with the
example.

It had been commented that 64-bit systems might make a difference,
such as SAS on 64-bit Windows as compared to 32-bit Windows.
That is not the case here. The IEEE floating point representation
used by SAS is 64-bit on all platforms. Only the z/OS implementation
of SAS uses the IBM representation.
-----------------------
End of Rick's clarification

It is not a bug in SAS mod function.

My original suspicion was an issue with SAS mod function itself. It is actually because the number 11,169,568,236,203,649 is larger than the maxim integer9,007,199,254,740,992  supported by SAS PC vesion. It is a not a bug since it is clearly stated in the document that there is a maximum integer without rounding supported by SAS.


I was building random generators on both SAS and Oracle. It is required that given the same initial number, the random generators on SAS and Oracle will produce the same series of pseudo random numbers.  Since it is impossible to match the SAS and Oracle results when the initial number is longer than 16 digits (Oracle integer supports 38 digits of precision), in my code I will throw an exception, stop the process and print a message similar to ORA-01438: "value larger than specified precision allowed for this column". 

Tuesday, May 29, 2012

A Bug in SAS MOD Function.

MOD( m, n)  is one of the most commonly used functions. It  returns the remainder of m divided by n and the result is deterministic. We would expect it to be calculated correctly by any data analysis software packages.

However, we have found an issue in SAS mod function.The calculation we tested is  mod(11169568236203649, 30269).  SAS returns different results than Oracle and R do. SAS returns 11731. Both Oracle and R return 11732.

SAS script (SAS Version 9.2) and the result.
data test; val=mod(11169568236203649, 30269) ; run;
11731
or
proc sql; select mod(11169568236203649, 30269) from dummy;
11731

Oracle SQL script and the result.
select mod(11169568236203649, 30269) from dual;
11732

R script and the result.
11169568236203649 %% 30269
11732

We have verified that 11732 is the correct answer using two independent approaches. You may try the above scripts on your own computer and let me know what you find out. ( I explain my approaches to verify that 11732 is the correct answer of mod(11169568236203649,30269) and why I bother to calculate mod(x, 30269) in other posts on this blog.)

Why Bother to Calculate Mod(x, 30269)?

I was trying to implement Wichmann-Hill random number generator in Oracle. The calculation of mod(x, 30269) is part of the algorithm. The algorithm is described in Algorithm AS 183: An Efficient and Portable Pseudo-Random Number Generator.

SAS Is Wrong! R and Oracle Are Right. MOD(11169568236203649, 30269)=11732



For function MOD(11169568236203649, 30269), SAS returns 11731 while both Oracle  and R  return 11732. I used the following two approaches to verify SAS Is Wrong.  Oracle and R are right.
Approach 1. The following two Oracle Queries show that 11732 is the remainder
SQL>  select ( 11169568236203649 -11732)/30269 vl from dual;
        369010150193.000000
SQL>  select ( 11169568236203649 -11731)/30269 vl from dual;
        369010150193.000033


Approach 2. The following PL/SQL scripts.
At first, I wanted to run the following PL/SQL Script 1. It took too long and I had to kill the process. Instead, I ran Script 2 shown below and it returned  11732 at the end of the loop.
PL/SQL Script 1.
set serveroutput on;
declare
remainder number;
begin
remainder:=11169568236203649;
while (remainder >30269)
loop
remainder:=remainder-30269;
end loop;
dbms_output.put_line(remainder);
end;
/
PL/SQL Script 2.
set serveroutput on;
declare
remainder number;
begin
remainder:=11169568236203649;
while (remainder>302690000000)
loop
remainder:=remainder-302690000000; /* equivalent of 10,000,000 loops of -30269 */
end loop;
dbms_output.put_line(remainder);
while (remainder>30269)
loop
remainder:=remainder-30269;
end loop;
dbms_output.put_line(remainder);
end;
/
At the end of the loop, it returns 11732.

Monday, May 28, 2012

Randomly Sample a Precise Number of Records



With Oracle dbms_random.random and row_number function, we can easily randomly select a certain number of records from the data.

The following scripts select 50 records randomly from each group (strata). First, a number number is generate for each record. Then a rank is generated by row_number function based on the random number for each strata. Finally, only records ranked top 50 in each strata are selected. The rank of random number is also random.

create table tbl_id_w_rnd as select id, strata, dbms_random.random() rnd from tbl_data;

create materialized view mv_precise_50_samples as
With tbl as
(
Select  a.*, row_number() over(partition by strata order by rnd) rnk from tbl_id_w_rnd  a
)
Select * from tbl where rnk<=50;
--test
select strata, count(1) from  mv_precise_50_samples group by strata order by strata;

Sunday, May 27, 2012

Calculate Gain Chart Using SQL in Oracle


The following script calculates the gain chart based on score and payment_ind (target variable, payment or non payment).  The mod function is to reduce the number of data points in the gain chart

with  tbl2 as
(select a.*, row_number() over(order by score desc ) risk_rank from tbl_testing_data a),
tbl3 as
( select a.*,
count(1) over(order by risk_rank) total_num,
sum(case when payment_ind='Y' then 1 else 0 end ) over(order by risk_rank) total_bad,
count(1) over(order by risk_rank)/count(1) over() pcnt_tot,
sum(case when payment_ind='Y' then 1 else 0 end ) over(order by risk_rank)/ sum(case when payment_ind='Y' then 1 else 0 end ) over()
pcnt_bad
 from tbl2 a
)
select * from tbl3 where mod(risk_rank,100)=1 order by risk_rank;

Remove Duplicates From Data


It is quite common to have duplicates in the data. From example, an application is submitted or a claim is filed  multiple times. With Oracle function row_number function, we can easily remove the duplicates.

The following SQL first calculates the rank of records by app_date for each app_id, then it only selects those records with ranks equals 1. The only earliest record for each app_id will be selected.

with tbl as
(
select a.*,
row_number() over(partition by app_id order by APP_DATE ) rnk  from CELL_PHONE_SERVICE_APPS a
)
select * from tbl where rnk=1;

If we want to keep the latest record for each app_id, we simply generate rank for each app_id by the descending order of app_date as shown below.


with tbl as
(
select a.*,
row_number() over(partition by app_id order by APP_DATE desc ) rnk  from CELL_PHONE_SERVICE_APPS a
)
select * from tbl where rnk=1;

Saturday, May 26, 2012

SAS vs SQL Database: Select an Enterprise Analytic Platform

Without doubt, SAS has the most sophisticated statistical models. However, as data analyitcs practitioners in real business world, we have realized that 80% or more of the time is spent on data preparation and model deployment. Thus, to decide which analytic platform to use in an company, we need to carefully assess factors including how the models will be used (online or offline), the staff's knowlege levels about SAS and SQL, data security, data size, costs, etc.

In my opinion, data should be stored, managed and analyzed in a SQL based database as much as possible. For example, with Oracle 11g, we can perform all the tasks including data collection, summary, manipulation, model building, model scoring, reporting, within a single environment. This in-database analytics approach greatly increases the productivity, manageability and security.