SAS macro to SQL migration

Reply
New Contributor
Posts: 2

SAS macro to SQL migration

Hi Team,

I am migrating the SAS code into SQL stored procedure . I am trying to migarte the sas macrocode into SQL quries

Can someome help me to write the SAS code into SQL ..following are my sas code -


/********************************************************************************************/
/* TO USE THIS CODE */
/* In your code, include the following lines: */
/* */
/* %include 'G:\MACROS\Telephone number validation.sas'; */
/* %valid_phone(library name, dataset name); */
/* e.g. %valid_phone(lib, master); or %valid_phone(strat, master_all); */
/* You do not have to copy this code into your code */
/* */
/* WHAT THIS CODE DOES */
/* Cleans the home, work and cell fields. Keeps the original in sztelhome_orig, etc */
/* and replaces the sztelhome etc with the cleaned versions */
/* Creates descriptions for the phone numbers (Valid, Invalid, Internationsal, Valid if JHB)*/
/* Creates indicators: has_valid_phone, has_cell, has_valid_phone_home, has_valid_phone_work */
/* has_valid_phone_other, number_valid_phones */
/* */
/********************************************************************************************/


%macro valid_phone(library, dataset);

libname lib2 'J:\MACROS\Phone number validation';

OPTIONS NOQUOTELENMAX;
options compress="YES";

data &library..codes_validation;
set lib2.codes_validation;

proc sort data=&library..codes_validation;
by nd_code la_code R_Start ;
run;

data &library..codes_valid;
format new_R_start new_R_end $10.;
set &library..codes_validation;
by nd_code la_code;
retain new_R_start;
if first.la_code then new_R_start=R_start;
if last.la_code then new_R_end=R_end;
if last.la_code then do;
if new_R_start="0000" and R_start^="0000" then R_start="0000";
end;
keep nd_la_code ND_Code LA_Code R_Start R_End DP EUType ;
if last.la_code then output;
run;

proc sort data=&library..codes_valid;
by nd_code la_code R_Start ;
run;


data tel_jhb ; /* Johannesburg numbers */
format tel_jhb $4000. ;
length tel_jhb $4000;
set &library..codes_valid (where=(nd_code="011" and R_start="0000" and R_end="9999")) end=last ;
retain tel_jhb " " ;
tel_jhb=compbl(tel_jhb||" "||" "||la_code);
tel_jhb=left(tel_jhb);
x=length(tel_jhb);
if last then call symput("tel_jhb",tel_jhb);
if last then output;
run;
%put &tel_jhb;


data tel1 ; /* Not Johannesburg */
format tel1 $3000. ;
length tel1 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and nd_la_code<="013980")) end=last ;
retain tel1 " " ;
tel1=compbl(tel1||" "||" "||nd_la_code);
tel1=left(tel1);
if last then call symput("tel1",tel1);
if last then output;
run;

%put &tel1;


data tel2 ;
format tel2 $3000. ;
length tel2 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and "013980"<nd_la_code<="018360")) end=last ;
retain tel2 " " ;
tel2=compbl(tel2||" "||" "||nd_la_code);
tel2=left(tel2);
if last then call symput("tel2",tel2);
if last then output;
run;
%put &tel2;


data tel3 ;
format tel3 $3000. ;
length tel3 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and "018360"<nd_la_code<="022451")) end=last ;
retain tel3 " " ;
tel3=compbl(tel3||" "||" "||nd_la_code);
tel3=left(tel3);
if last then call symput("tel3",tel3);
if last then output;
run;
%put &tel3;


data tel4 ;
format tel4 $3000. ;
length tel4 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and "022451"<nd_la_code<="033386")) end=last ;
retain tel4 " " ;
tel4=compbl(tel4||" "||" "||nd_la_code);
if last then call symput("tel4",tel4);
if last then output;
run;
%put &tel4;

data tel5 ;
format tel5 $3000. ;
length tel5 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and "033386"<nd_la_code<="043740")) end=last ;
retain tel5 " " ;
tel5=compbl(tel5||" "||" "||nd_la_code);
tel5=left(tel5);
if last then call symput("tel5",tel5);
if last then output;
run;
%put &tel5;

data tel6 ;
format tel6 $3000. ;
length tel6 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and "043740"<nd_la_code<="053742")) end=last ;
retain tel6 " " ;
tel6=compbl(tel6||" "||" "||nd_la_code);
tel6=left(tel6);
if last then call symput("tel6",tel6);
if last then output;
run;
%put &tel6;

data tel7 ;
format tel7 $3000. ;
length tel7 $3000;
set &library..codes_valid (where=(nd_code^="011" and R_start="0000" and R_end="9999" and "053742"<nd_la_code<="059512")) end=last ;
retain tel7 " " ;
tel7=compbl(tel7||" "||" "||nd_la_code);
tel7=left(tel7);
if last then call symput("tel7",tel7);
if last then output;
run;
%put &tel7;

data telA ;
format tela $3000. ;
length tela $3000 ;
set &library..codes_valid (where=(nd_code^="011" and ("0000"<R_start or "0000"<=R_end<"9999"))) end=last ;
retain tela " " ;
tela=compbl(tela||" "||" "||compress(nd_la_code||R_start||R_end));
tela=left(tela);
if last then call symput("tela",tela);
if last then output;
run;
%put &telA;


data tel_manual;
format tel_manual_5 tel_manual_6 tel_manual_7 tel_manual_8 $3000. ;
length tel_manual_5 tel_manual_6 tel_manual_7 tel_manual_8 $3000 ;
set &library..codes_validation (where=(EUType="Manual")) end=last ; /* Do not use "&library..codes_valid" */
retain tel_manual_5 " " tel_manual_6 " " tel_manual_7 " " tel_manual_8 " ";
l_ND_Code_orig=length(nd_la_code);

if l_ND_Code_orig=5 then do;
tel_manual_5 =compbl(tel_manual_5||" "||" "||compress(ND_Code_orig));
tel_manual_5=left(tel_manual_5);
end;

if l_ND_Code_orig=6 then do;
tel_manual_6 =compbl(tel_manual_6||" "||" "||compress(ND_Code_orig));
tel_manual_6=left(tel_manual_6);
end;

if l_ND_Code_orig=7 then do;
tel_manual_7 =compbl(tel_manual_7||" "||" "||compress(ND_Code_orig));
tel_manual_7=left(tel_manual_7);
end;

if l_ND_Code_orig=8 then do;
tel_manual_8 =compbl(tel_manual_8||" "||" "||compress(ND_Code_orig));
tel_manual_8=left(tel_manual_8);
end;

if last then do;
call symput("tel_manual_5",tel_manual_5);
call symput("tel_manual_6",tel_manual_6);
call symput("tel_manual_7",tel_manual_7);
call symput("tel_manual_8",tel_manual_8);
output tel_manual;
end;

run;

/* Run macro for one telephone number at a time and merge files at the end for all the telephone numbers (see bottom of macro) */

data &library..cust_final;
set &library..&dataset(keep=wclientcode szaccountnumber sztelhome sztelwork sztelother);
run;

%macro testtel (tel) ;

data &library..&tel.;
format find_ last_4 r_start1 r_end1 $10. ;
set &library..cust_final (keep=&tel szaccountnumber wclientcode ); /* keep only one telephone number at a time and also the variable used to merge */
&tel._orig=&tel; /* Keep original telephone number */
pos_=indexc(&tel.,"\EXT_WHx");
pos2=indexc(&tel.,"/");
pos3=indexc(&tel.,"*");
count_=count(&tel.,"/");
count__=count(&tel.,"-");

count2=count(&tel.,")");
count3=count(&tel.,"(");


&tel.=compress(&tel.,"=<>()/\ABCDEFGHIJKLMNOPQRSTUVWXYZqwertyuiopasdfghjklzxcvbnm .!{}[]*&^%$£!+_-~#@':;,|`¬");

/*////////////////////////////*/
/*///////////////////////////*/
/*DM Change*/
/*add leading zero to all numbers lenth 9 and not starting with 0*/
len = length(&tel.);
check1 = substr(&tel.,1,1);
if len = 9 and check1 ne 0 then &tel=compress("0"||&tel.);
/*replace international code 27 with 0 if length is 11 and 3rd digit is not 0*/
if len = 11 and substr(&tel.,1,2) = "27" and substr(&tel.,1,3) ne "0" then &tel=compress("0"||substr(&tel.,3,9));
/*////////////////////////////*/
/*///////////////////////////*/

digit_1=substr(&tel.,1,1);

if digit_1^="0" and pos_>7 then &tel.=substr(&tel.,1,7);
if digit_1="0" and pos_>10 then &tel.=substr(&tel.,1,10);

if digit_1^="0" and pos2>7 then &tel.=substr(&tel.,1,7);
if digit_1="0" and pos2>10 then &tel.=substr(&tel.,1,10);

if digit_1^="0" and pos3>7 then &tel.=substr(&tel.,1,7);
if digit_1="0" and pos3>10 then &tel.=substr(&tel.,1,10);


if digit_1^="0" and count_>1 then &tel.=substr(&tel.,1,7);
if digit_1="0" and count_>1 then &tel.=substr(&tel.,1,10);

 

/* Count digits */
number_digits=length(&tel.);


if number_digits=7 then do;
&tel=compress("011"||&tel.);
comment="Test if JHB number";
end;

next_3=substr(&tel,4,3);


first_2=substr(&tel,1,2);
first_3=substr(&tel,1,3);
first_4=substr(&tel,1,4);
first_5=substr(&tel,1,5);
next_3=substr(&tel,4,3);
first_2=substr(&tel,1,2);
first_6=substr(&tel,1,6);
count2=count(&tel,")");
count3=count(&tel,"(");
last_3=substr(&tel,8,3);
first_7=substr(&tel,1,7);
last_7_SA=substr(&tel,5,7);

 


if first_2="11" and number_digits=9 then do; /*JHB number missing first 0 */
&tel=compress("0"||&tel);
comment="Test if JHB number" ;
end;
if first_4="2711" or first_4="+271" then &tel=compress("011"||last_7_SA);
if last_3="011" and digit_1^="0" then &tel=compress("011"||first_7);
if last_3="012" and digit_1^="0" and number_digits=10 then &tel=compress("012"||first_7); /* eg 7854966(012) */
if digit_1="9" and count2>=1 and count3=0 then &tel=substr(&tel,2); /* 9 used instead of bracket eg 9011)7865844 */
if first_2 in ("72","73","74","76","78","79","80","81","82","83","84","86","88") and number_digits=9 then &tel=compress("0"||&tel); /* cellphone missing first 0 */

&tel._descr="XXXXXXXXXXXXXXXXXXXX";
number_digits=length(&tel.); /* Count again number of digits */
&tel.=compress(&tel.);
first_2=substr(&tel,1,2); /* Make again variables with first digits */
first_3=substr(&tel,1,3);
first_4=substr(&tel,1,4);
first_5=substr(&tel,1,5);
first_6=substr(&tel,1,6);
first_7=substr(&tel,1,7);
first_8=substr(&tel,1,8);

 

if number_digits=10 and first_3 in ("071","072","073","074","076","078","079","080","082","083","084") then &tel._descr="Valid"; /* not else if */
else if number_digits=10 and first_4 in ("0860", "0861", "0866","0881") then &tel._descr="Valid" ;
else if first_2="09" and number_digits>=10 then &tel._descr="International";


if first_3="011" then find_=index(("&tel_jhb"),compress(next_3));
find_=compress(find_);
if find_>0 and comment="Test if JHB number" then &tel._descr="Valid if JHB number";


if number_digits=10 and digit_1="0" then do;
/* Test for JHB numbers */
if first_3="011" then find_=index(("&tel_jhb"),compress(next_3));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";

/* Test Not JHB numbers. Have to break up codes in 7 parts from "0000" to "9999" */

if first_3^="011" and "011988"<compress(first_6)<="013980" then
find_=find("&tel1",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";


else if first_3^="011" and "013980"<compress(first_6)<="018360" then
find_=find("&tel2",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";


else if first_3^="011" and "018360"<compress(first_6)<="022451" then
find_=find("&tel3",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";

else if first_3^="011" and "022451"<compress(first_6)<="033386" then
find_=find("&tel4",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";

else if first_3^="011" and "033386"<compress(first_6)<="043740" then
find_=find("&tel5",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";

else if first_3^="011" and "043740"<compress(first_6)<="053742" then
find_=find("&tel6",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";

else if first_3^="011" and "053742"<compress(first_6)<="059512" then
find_=find("&tel7",compress(first_6));
find_=compress(find_);
if find_>0 then &tel._descr="Valid";


else if first_3^="011" then do;
last_4=substr(&tel,7,4);
find_=find("&tela",compress(first_6));
find_=compress(find_);
if find_>0 then do;
find_2=find_+6;
find_3=find_2+4;
r_start1=left(compress(substr("&tela",find_2,4)));
r_end1=left(compress(substr("&tela",find_3,4)));
if R_start1<=last_4<=R_end1 then &tel._descr="Valid";

end;

/*Manual */
if first_5 in ("02062","02072","02082","02762","05232","05242","05282","05292","05352","05442","05462","05472","05482",
"05492","05542","05552","05562","05572","05582","05602","05612","05772","05962")
and number_digits=10 then do;
find_5=find("&tel_manual_5",compress(first_5));
find_=compress(find_5);
if find_>0 then &tel._descr="Valid";
end;

if first_6 in ("014092","014252","014262","014452","014472","014492","015482","015912","051722","051732","051742",
"051762","051782","051952", "051962","051982","052112","052122", "052332", "053212","053232","053242","053272","053612","053642",
"053652","053682","053862", "053922","053932","053952","053972","053982","053992","054902","058642","058652","059512")
and number_digits=10 then do;
find_6=find("&tel_manual_6",compress(first_6));
find_=compress(find_6);
if find_>0 then &tel._descr="Valid";
end;


if first_7 in ("0142592","0152302","0172182","0183672","0152302","0532092","0533812","0533822", "0533832", "0533912", "0535892", "0536612",
"0536712","0536732", "0536912", "0539362", "0539372","0539982","0545112", "0545312", "0547412", "0549712")
and number_digits=10 then do;
find_=find("&tel_manual_7",compress(first_7));
find_7=compress(find_7);
if find_>0 then &tel._descr="Valid";
end;


if first_8 in ("01523252","05187192")
and number_digits=10 then do;
find_8=find("&tel_manual_8",compress(first_8));
find_=compress(find_8);
if find_>0 then &tel._descr="Valid";
end;

 


end;
end;


/*keep &tel &tel._descr szaccountnumber ;*/

if &tel._descr="XXXXXXXXXXXXXXXXXXXX" then &tel._descr="Not Valid";

keep &tel &tel._descr wclientcode szaccountnumber &tel._orig ;

run;
%mend;

%testtel (sztelhome); /* tel1 is name of variable in the dataset */
%testtel (sztelwork); /* tel2 is name of variable in the dataset */
%testtel (sztelother); /* tel3 is name of variable in the dataset */

proc sort data=&library..sztelhome ;
by wclientcode szaccountnumber;
proc sort data=&library..sztelwork;
by wclientcode szaccountnumber;
proc sort data=&library..sztelother;
by wclientcode szaccountnumber;
proc sort data=&library..&dataset;
by wclientcode szaccountnumber;
run;

data &library..&dataset;
merge &library..&dataset(in=a drop=sztelhome sztelwork sztelother) &library..sztelhome &library..sztelwork &library..sztelother;
by wclientcode szaccountnumber;
if a;
run;

proc datasets library=&library nolist;
delete cust_final sztelhome sztelwork sztelother;
quit;

data &library..&dataset;
set &library..&dataset;
has_valid_phone_home=(sztelhome_descr='Valid');
has_valid_phone_work=(sztelwork_descr='Valid');
has_valid_phone_other=(sztelother_descr='Valid');
number_valid_phones=has_valid_phone_home + has_valid_phone_work + has_valid_phone_other;

has_cell=(sztelhome_descr='Valid' and substr(sztelhome,1,3) in('071','072','073','074','076','078','079','081','082','083','084','085') )
OR (sztelwork_descr='Valid' and substr(sztelwork,1,3) in('071','072','073','074','076','078','079','081','082','083','084','085') )
OR (sztelother_descr='Valid' and substr(sztelother,1,3) in('071','072','073','074','076','078','079','081','082','083','084','085') );
has_valid_phone=(sztelhome_descr in('Valid','International'))
OR (sztelwork_descr in('Valid','International'))
OR (sztelother_descr in('Valid','International'));
run;

%mend;

Respected Advisor
Posts: 3,887

Re: SAS macro to SQL migration

What do you expect is now going to happen? What have you done so far?

 

You will need to ask directed questions with a much smaller scope; and these questions will need to demonstrate your own effort and thinking.

 

 

Super User
Posts: 3,102

Re: SAS macro to SQL migration

[ Edited ]

Warning - personal rant 

 

In my humble and professional opinion, this type of data manipulation is far better off left in SAS. The result in SQL would be horrendous and very hard to get the same results, and would probably be only understandable by the person who wrote it. I've seen people attempt this type of processing in SQL stored procedures and all you end up with is a complete dog's breakfast and most likely run slow as a dog as well because you will need to use SQL cursors for some conditional logic.

 

Super User
Posts: 6,932

Re: SAS macro to SQL migration

Leave that in SAS, or you might end up strangling yourself with your own intestines in a rush of madness.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,395

Re: SAS macro to SQL migration

Yep, there are plenty of people out there that can help you, generally it will cost sommewhere from £50 per hour upwards.  I will tell you now however looking at that code, that it is typical of macro programming, i.e. a total mess.  Its next to impossible for a human to read it, i.e. no formatting, block indentations, mixed casings etc.  and at a breif glance half the code is irrelevant.  Time to get out the Functional Design Specification and build it new using clear SDLC practices.

Ask a Question
Discussion stats
  • 4 replies
  • 104 views
  • 1 like
  • 5 in conversation