Calcite | Level 5

## ISIN code convert from datastep to sql

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)

(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

9 REPLIES 9
Tourmaline | Level 20

## Re: ISIN code convert from datastep to sql

Why do you want to do it in SQL?

Tthe data step works, no?

Data never sleeps
Calcite | Level 5

## Re: ISIN code convert from datastep to sql

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

Opal | Level 21

## Re: ISIN code convert from datastep to sql

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.

Calcite | Level 5

## Re: ISIN code convert from datastep to sql

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

``````%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); ``````

Opal | Level 21

## Re: ISIN code convert from datastep to sql

@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;
``````
Calcite | Level 5

## Re: ISIN code convert from datastep to sql

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

Opal | Level 21

## Re: ISIN code convert from datastep to sql

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

Calcite | Level 5

## Re: ISIN code convert from datastep to sql

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

Opal | Level 21

## Re: ISIN code convert from datastep to sql

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.

Discussion stats
• 9 replies
• 3431 views
• 0 likes
• 3 in conversation