turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- ISIN code convert from datastep to sql

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 01:38 PM

**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**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 03:38 PM

Why do you want to do it in SQL?

Tthe data step works, no?

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 05:39 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 07:33 PM - edited 10-15-2015 07:44 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 08:46 PM - edited 10-15-2015 08:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 10:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-15-2015 11:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-16-2015 01:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-16-2015 10:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-16-2015 06:19 PM - edited 10-16-2015 06:20 PM

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):

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/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.