Thank you Jim and Howard for the wonderful explanation and solution.

Jim,

what you have suggested works exactly the way i want it. as it is too long, i cut the 1st few lines of the macro.

%macro ADD_ERR_MSG(VTRL_NAME, VPAT_INIT, VPAT_DOB, VPAT_NO, VV_NAME, VT_NAME, VERR_MSG); %if &VV_NAME = . and &VT_NAME = . %then %do; /* Customer Level*/ proc sql; Insert into Error_Tab

and the data step that calls it :

data _null_; set temp; CALL EXECUTE ('%ADD_ERR_MSG(' || TRL_NAME || ',' || PAT_INIT || ',' || PAT_DOB || ',' || PAT_NO || ',' || V_NAME || ',' || T_NAME || ', "+ sign exists.");'); run;

I believe proc sql; should work correctly in the macro as each call is 1 exclusive insertion of record. as such, even if the macro is Retain until end of data step, it should not affect anything.

Howard

I am using your suggestion for those checkings that are not so direct, where it will insert the errors to a err_tab_temp before appending all of them to the master Error_tab. the reason why i am creating a macro is so that my other team member just call a common interface to insert the error. With that, they can use PROC SQL Select INTO var and call the macro after supplying the parameters. They do not need to create a data set for it. i mean, i am thinking along this line.

thank you for all your help Boon Yiang