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
... View more