Convert SAS macro to SQL

[Joe75]

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;

[SASKiwi]

The macro only contains text string manipulation logic with no data input or output. You've missed out the rest of the processing.

Having said that, the only way I can see this being done in MS SQL is as a stored procedure using cursors and looping logic as "normal" SQL has no concept of looping. This is extremely ugly and I can't recommend you attempting it unless you are familiar with database stored procedures. 

[Joe75]

Thank you very much for your advice!

[ballardw · Accepted solution]

You may get a better response by providing 1) a description of what that code is supposed to do, 2) examples of the data before the process and 3) what the data looks like afterward.

Loops like that are really not very natural to SQL and it may be better to redesign from scratch instead of trying to do a line by line translation of data step code to SQL (which generally doesn't work well.)

[Joe75]

Got it! Thank you for the advice, really appreciate it!