ISIN code convert from datastep to sql

[siddharthpeesary]

recently I wrote a programming in DATASTEP but i want to convert in proc SQL which I am not familiar with . can any one translate the code...

%macro ISINVERIFICATION(infile=,isinfield=);
DATA outfile;
SET &infile;
RTOTAL=0;TOTAL=0; length x $25;
DO J = 1 TO 11;
k=rank(upcase(substr(&isinfield,J,1)));
IF k GE 65 and k LE 95 then k=k-55;
IF k GE 48 and k LE 57 then k=k-48;
x=cats(x,k);
if K >= 10 then cnt=2; else if K < 10 then cnt=1;
RTOTAL=RTOTAL+cnt;
END;
do j=1 to RTOTAL;
y=substr(x,J,1);
if mod(RTOTAL,2)=0 then if mod(j,2)=0 then y=y*2;
if mod(RTOTAL,2)^=0 then if mod(j,2)^=0 then y=y*2;
TOTAL=TOTAL + int(y/10) + MOD(y,10);
if mod(Total + substr(&isinfield,12,1),10) = 0 then isinflag = 'yes'; else isinflag = 'no';
end;
DROP J x y cnt rtotal total k ;
RUN;
%mend;

logic for above programming:

APPLE: ISIN US0378331005, expanded from CUSIP 037833100 The main body of the ISIN is the original CUSIP, assigned in the 1970s. The country code "US" has been added on the front, and an additional check digit at the end. The country code indicates the country of issue. The check digit is calculated in this way.

Convert any letters to numbers:

U = 30, S = 28. US037833100 -> 3028037833100.

Collect odd and even characters:

3028037833100 = (3, 2, 0, 7, 3, 1, 0), (0, 8, 3, 8, 3, 0)

Multiply the group containing the rightmost character (which is the FIRST group) by 2:

(6, 4, 0, 14, 6, 2, 0)

Add up the individual digits:

(6 + 4 + 0 + (1 + 4) + 6 + 2 + 0) + (0 + 8 + 3 + 8 + 3 + 0) = 45

Take the 10s modulus of the sum:

45 mod 10 = 5

Subtract from 10:

10 - 5 = 5

Take the 10s modulus of the result (this final step is important in the instance where the modulus of the sum is 0, as the resulting check digit would be 10).

5 mod 10 = 5

So the ISIN check digit is five.

OUTPUT:  It checks a ISIN number created a FLAG which shows pass,fail and blank

It will be very helpful and your help is much appreciated

[LinusH]

Why do you want to do it in SQL?

Tthe data step works, no?

[siddharthpeesary]

because I want to keep SQL(ISIN) in hadoop (contains sas loader) in data validation rule engine.. where it does not accepts data step function.

so i want to convert in sql  for sure.

My data step works perfect..

Thanks

[Patrick]

Can you please provide some mock-up data (created using a data step) which works with your macro. This will allow us to actually test what we might propose.

Be aware that only a sub-set of SAS functions can be passed to Hadoop (Hive) for processing.

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p010gv8z4k5kvin1om...

Because the MOD() function is not in the list I would assume that pass-through SQL will be required. That will make it a bit harder for most of us as one needs actual access to Hadoop in order to test correctness of the syntax.

[siddharthpeesary]

Thanks so much.I can do the part for hadoop .. my main objective is to write it in proc sql.

please see my attached files

%macro importdata(infile=,outfile=,type=,get=,filter=);
proc import
datafile=&infile
dbms=&type
out=&outfile 
 replace;
 getnames=&get;
&filter;
run;
%mend importdata;
/*please run the sas programming then you will get out put 
 please let me know if there are any mis-understandings*/

%importdata(infile='(PATHNAME)\input_isin.xlsx',outfile=test_isin,type=XLSX,get=YES,filter=guessingrows=10000);



%macro ISINVERIFICATION(infile=,isinfield=); 
DATA out_isin; 
SET &infile; 
RTOTAL=0;TOTAL=0; length x $25;
DO J = 1 TO 11;
k=rank(upcase(substr(&isinfield,J,1))); 
IF k GE 65 and k LE 95 then k=k-55; 
IF k GE 48 and k LE 57 then k=k-48; 
x=cats(x,k);
if K >= 10 then cnt=2; else if K < 10 then cnt=1; 
RTOTAL=RTOTAL+cnt;
END; 
do j=1 to RTOTAL;
y=substr(x,J,1); 
if mod(RTOTAL,2)=0 then if mod(j,2)=0 then y=y*2;
if mod(RTOTAL,2)^=0 then if mod(j,2)^=0 then y=y*2;
TOTAL=TOTAL + int(y/10) + MOD(y,10);
if mod(Total + substr(&isinfield,12,1),10) = 0 then isinflag = 'yes'; else isinflag = 'no';
end;
  DROP J x y cnt rtotal total k ; 
RUN;
%mend;

%ISINVERIFICATION(infile=test_isin,isinfield=ISIN); 

[Patrick]

@siddharthpeesary

O.K., now I understand what you're doing. I wouldn't know how to convert your data step into reasonable standard SQL code.

I believe what you need to do is implement your check as a user defined function directly in Hadoop which you then can call via a pass-through SQL out of SAS.

Below example demonstrates the concept by creating a SAS function. I lack the Hadoop experience to give you more guidance but searching through the Internet I believe this is a possible approach.

proc fcmp outlib=work.funcs.check_isin;
  function check_isin(isin $) $;
    RTOTAL=0;
    TOTAL=0;
    length x $25 y 8 isinflag $3;
    DO J = 1 TO 11;
      k=rank(upcase(substr(ISIN,J,1)));
      IF k GE 65 and k LE 95 then
        k=k-55;
      IF k GE 48 and k LE 57 then
        k=k-48;
      x=cats(x,k);
      if K >= 10 then
        cnt=2;
      else if K < 10 then
        cnt=1;
      RTOTAL=RTOTAL+cnt;
    END;

    do j=1 to RTOTAL;
      y=substr(x,J,1);
      if mod(RTOTAL,2)=0 then if mod(j,2)=0   then y=y*2;
      if mod(RTOTAL,2)^=0 then if mod(j,2)^=0 then y=y*2;
      TOTAL=TOTAL + int(y/10) + MOD(y,10);
/*      if mod(Total + substr(ISIN,12,1),10) = 0 then*/
      digit=substrn(ISIN,12,1);
      if not findc(digit,'0123456789') then digit='0';
      if mod(Total +inputn(digit,8.),10) = 0 then
        isinflag = 'yes';
      else isinflag = 'no';
    end;

    return(isinflag);
  endsub;
run;

quit;

options cmplib=work.funcs;

proc sql;
  create table want as
  select 
    isin, 
    check_isin(isin) as flag
  from test_isin
  ;
quit;

[siddharthpeesary]

These FCMP can be used in hadoop sas?? 

if it is.... the logic inside the function is in datastep..it can be executed in hadoop(sas loader)?

Thanks again

[Patrick]

Hmmm... This was actually only to demonstrate the concept. I was more thinking of creating a UDF without SAS tools and then use this UDF within pass-through SQL.

Something like: https://cwiki.apache.org/confluence/display/Hive/HivePlugins

[siddharthpeesary]

So you are saying we have to write the code using HIVE functions to create UDF or we can pull sas functions to it?

Thanks

[Patrick]

I'm just throwing ideas here! I don't have enough real experience working with Hadoop out of SAS to do something else.

Yes, my thinking is to write a HIVE function and then to call this function out of pass-through SQL.

There is the SAS In-Database Code Accelerator for Hadoop (I believe needs a separate licence):

http://support.sas.com/documentation/cdl/en/indbug/68170/HTML/default/viewer.htm#n13tmaloxv64n3n1ixv...

Never used it but it appears it would allow you to publish SAS DS2 programs to Hadoop. That could be another option for you.

https://support.sas.com/documentation/cdl/en/proc/67916/HTML/default/viewer.htm#n0ox2hnyx7twb2n13200...

https://support.sas.com/documentation/cdl/en/ds2ref/68052/HTML/default/viewer.htm#titlepage.htm

DS2 is a new language (also with a lot of syntactical similarities to Base SAS) and it will take you a bit to skill-up with it.