CODEWITHSUNDEEP
sas
user2462699
user2462699

Reputation: 439

SAS &SYSERRORTEXT variable removing quote to use in SQL

I'm trying to use SAS system variables to track batch program execution (SYSERRORTEXT, SYSERR, SYSCC, ...)

I want to insert those in a dataset, using PROC SQL, like this :

Table def :

PROC SQL noprint;
CREATE TABLE WORK.ERROR_&todaydt. (
    TRT_NM VARCHAR(50)
    ,DEB_EXE INTEGER FORMAT=DATETIME.
    ,FIN_EXE INTEGER FORMAT=DATETIME.
    ,COD_ERR VARCHAR(10)
    ,LIB_ERR VARCHAR(255)
    ,MSG_ERR VARCHAR(1000)
)
;
RUN;
QUIT;

Begin execution : 

PROC SQL noprint;
    INSERT INTO WORK.ERROR_&todaydt. VALUES("&PGM_NAME", %sysfunc(datetime), ., '', '', '';
RUN;
QUIT;

End execution :

PROC SQL noprint;
    UPDATE WORK.ERROR_&todaydt. 
        SET 
        FIN_EXE = %sysfunc(datetime())
        ,COD_ERR = "&syserr"
        ,LIB_ERR = ''
        ,MSG_ERR = "&syserrortext"
    WHERE TRT_NM = "&PGM_NAME"
    ;
RUN;
QUIT;

The problem occurs with system variable &syserrortext. which may contains special char, espcially single quote ('), like this

Code example for problem :

DATA NULL;
    set doesnotexist;
RUN;

and so, &syserrortext give us : ERROR: Le fichier WORK.DOESNOTEXIST.DATA n'existe pas.

my update command is failing with this test, so how i can remove special chars from my variable &syserrortext ?

Upvotes: 0

Views: 423

Answers (2)

Allan Bowe
Allan Bowe

Reputation: 12691

One approach, which avoids the need to remove special characters, is to simply use symget(), eg as follows:

,MSG_ERR = symget('syserrortext')

Upvotes: 1

Tom
Tom

Reputation: 51566

Make sure to quote the value. First use macro quoting in case the value contains characters that might cause trouble. Then add quotes so that the value becomes a string literal. Use the quote() function to add the quotes in case the value contains quote characters already. Use the optional second parameter so that it uses single quotes in case the value contains & or % characters.

,MSG_ERR = %sysfunc(quote(%superq(syserrortext),%str(%')))

Upvotes: 1

Related Questions