Not sure if you can do it from SQL but here's some data step that handles it using arrays:
/* Create Sample Data */
Data have;
infile cards expandtabs;
input debt_code ad_title $ ad_inits $ ad_name $ ad_dob ad_address1 $ ad_address2 $ ad_address3 $ ad_address4 $ ad_address5 $ ad_postcode $ ad_type $
;
datalines ;
431982404 Mr Michael Esposito . 2St.AubynsCottages ColeLaneOckley Dorking Surrey . RH55SX DB
426336236 Miss Joanne O'brien . DeerePlace Cardiff . . . CF54NL DB
418126603 Mrs Sarah Weston . 1FalaVillage . . . . EH375SY DB
433349933 . Hmp Park . Gloucestershire . . . . . DB
422054429 Mrs . McCormick . 52FoxStreet Stockport Cheshire . . SK39JY DB
;
run;
/* Debugging option */
options symbolgen ;
/* Set the lenght of the error msssage variable */
/* Do this to handle when the addative error messages exceed the length of the error message variable */
%let errorMsgLength=50 ;
data want ;
/* assing length of errormsg variable */
length
errormsg $&errorMsgLength ;
set have ;
/* create array to hold error messages */
array msg(8) $ ("Initials" "Surname" "Address1" "Address2" "Address3" "Address4" "Address5" "PostCode") ;
/* create arrary to hold character input variables that you want to check */
/* note you will need to do something similar for numeric variables */
array values(8) $ ad_inits ad_name ad_address1-ad_address5 ad_postcode ;
/* Assign iniital value to errorMsg */
errormsg="Missing" ;
/* Flag to capture when more errors are added to errorMsg than it can handle */
additionalErrorsFlag="N" ;
/* text to insert into errorMsg when it exceeds the maximum it can handle */
exceedErrorMsg="*** ERRORS EXCEEDED ***" ;
/* Loop through array */
do i=1 to dim(values) ;
/* Check if element is empty */
if values(i)="" then do ;
/* Check if adding an error msg will exceeed the lenght of errorMsg */
if length(errormsg)+length(msg(i))>&errorMsgLength then do ;
/* bump i so we drop out of the loop early */
/* no point in checking additional fields as the errorMsg is now full */
i=dim(msg) ;
/* set the additional errors flag */
additionalErrorsFlag="Y" ;
/* overwrite the end of the error message with a note indicating additional errors not listed */
substr(errormsg,&errorMsgLength-length(exceedErrorMsg)) = exceedErrorMsg ;
end ;
/* if errorMsg has room for addtional error then add it to the end */
else do ;
errormsg=cats(errormsg,"-",msg(i)) ;
end ;
end ;
end ;
run ;
... View more
