Would like to seek advice to convert the SAS Macro script below, to MS SQL format: %MACRO RETAIN_DIGIT; CLEANUP_VAR=MOBILE_NO; %DO I=1 %TO 30; %LET J=%EVAL(&I+1); %LET H=%EVAL(&I-1); IF LENGTH(MOBILE_NO)>=&I THEN DO; IF COMPRESS(SUBSTR(MOBILE_NO,&I,1),"","D")^="" THEN DO; IF SUBSTR(MOBILE_NO,&I,1) IN ("O","Q") AND LENGTH(COMPRESS(SUBSTR(MOBILE_NO,&J),"","KD"))>=9 THEN IF LENGTH(COMPRESS(SUBSTR(COMPRESS(SUBSTR(MOBILE_NO,&J)),1,2),"","KD"))=2 THEN IF 10<=INPUT(SUBSTR(COMPRESS(SUBSTR(MOBILE_NO,&J)),1,2),$2.)<=19 THEN CLEANUP_VAR=TRANSLATE(CLEANUP_VAR,"0",SUBSTR(CLEANUP_VAR,&I,1)); ELSE CLEANUP_VAR=TRANSLATE(CLEANUP_VAR," ",SUBSTR(CLEANUP_VAR,&I,1)); ELSE CLEANUP_VAR=TRANSLATE(CLEANUP_VAR," ",SUBSTR(CLEANUP_VAR,&I,1)); ELSE CLEANUP_VAR=TRANSLATE(CLEANUP_VAR," ",SUBSTR(CLEANUP_VAR,&I,1)); END; %IF &I>=3 %THEN %DO; ELSE IF COMPRESS(SUBSTR(MOBILE_NO,&I,1),"","KD")^="" AND LENGTH(COMPRESS(SUBSTR(MOBILE_NO,&J),"","KD"))>=7 THEN DO; IF COMPRESS(SUBSTR(MOBILE_NO,&I,1),"","KD")^="" AND SUBSTR(MOBILE_NO,&H,1)="/" AND (SUBSTR(MOBILE_NO,&J,1)="-" OR SUBSTR(MOBILE_NO,&J,1)=" ") THEN CLEANUP_VAR=SUBSTR(CLEANUP_VAR,1,&H)||" "||SUBSTR(CLEANUP_VAR,&J); END; %END; END; %END; CLEANUP_VAR=STRIP(COMPBL(CLEANUP_VAR)); %MEND;
... View more
