Hi Guys , I'm new to sas programming and now im trying to understand the sas program wriiten by someone - can you please give me idea how below statment works ? %do i=1 %to 60 %by 1; %do j=&i-1 %to &i-1; ... View more

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