topic Re: Convert SAS macro to SQL in SAS Programming

Convert SAS macro to SQL

Joe75 — Thu, 05 Aug 2021 02:17:15 GMT

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

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;

Re: Convert SAS macro to SQL

SASKiwi — Thu, 05 Aug 2021 02:36:09 GMT

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.

Re: Convert SAS macro to SQL

ballardw — Thu, 05 Aug 2021 02:41:24 GMT

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

Re: Convert SAS macro to SQL

Joe75 — Thu, 05 Aug 2021 02:48:38 GMT

Thank you very much for your advice!

Re: Convert SAS macro to SQL

Joe75 — Thu, 05 Aug 2021 03:23:22 GMT

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