Data validation and writing the error record in a variable

[David_Billa]

I've  a data as follows.

data have;
  infile cards dsd dlm='|' truncover;
  input FLT_LAYR_NM :$10. FYP_NM :$30. _TEAM_NM :$30. ;
cards;
DIS|Consistencycheck|Business
DID|Rangecheck|Client
DID|' '|Client
DID|Rangecheck|Employee
;
run;

In the above dataset if FYP_NM is missing or _TEAM_NM is not in Business or Employee then I Need the dataset as follows. It means I've to enter the error_id which we can hard code and for the Error_record field I Need to capture the error record in this field. It is Ok if I capture only one record in that field even though there are multiple records which has faailed under same Validation. This is only the sample. I've multiple Validation for different datasets in real life.

Error_id	Error_record
1001	DID '  ' Client
1002	DID Rangecheck Employee

Appericiate if you could help me tackle this scenario?

[PeterClemmensen]

how come DID Rangecheck Employee should be in the Error Data? 

[David_Billa]

Last record has failed the range check and therefore it is considered as
error and I need to capture that record in the respective variable per my
output dataset as shown.

I'm looking for mechanism to tackle this requirement

[PeterClemmensen]

Then why isn't  DID|Rangecheck|Client in there?

[andreas_lds]

You need to write code for each check you want, so at least one if-statement with an output-statement writing to the errors-dataset. And yes, that could be an ugly amount of lines to write.

[David_Billa]

I agree with you, may I know the other mechanism to address this
requirement?

[Patrick]

@David_Billa 

You could do something as below

data want(drop=Error_ID) error;
  length Error_ID $4;
  infile cards dsd dlm='|' truncover;
  input FLT_LAYR_NM :$10. FYP_NM :$30. _TEAM_NM :$30. ;
  output want;
  /* error table */
  if missing(FYP_NM) then
    do;
      Error_ID='1001';
      output error;
    end;
cards;
DIS|Consistencycheck|Business
DID|Rangecheck|Client
DID|' '|Client
DID|Rangecheck|Employee
;
run;

Please note that this will create a record per error so if you've got more than 1 error in a row you'll get multiple entries in your error table.

What's sometimes done is creating an Error and an Exception table. The exception table stores all all the 'errors' so here you've got an entry per error found (error, column with the error, key to error record in Error table).

The Error table simply contains the full record from source - so only a single row whether there has been 1 or n errors.

[David_Billa]

Could you please help me with one example to know how it can be tackled
with error and exemption table?

[David_Billa]

Please ignore my previous comment. Last record should not be in the output dataset. Instead second and third record should be in the output as it has value other than 'Business' or 'Employee'

[Tom · Accepted solution]

Something like this:

data have;
  infile cards dsd dlm='|' truncover;
  input FLT_LAYR_NM :$10. FYP_NM :$30. _TEAM_NM :$30. ;
cards;
DIS|Consistencycheck|Business
DID|Rangecheck|Client
DID| |Client
DID|Rangecheck|Employee
;

data want;
  length Error_id 8	Error_record $200;
  keep Error_id	Error_record ;
  set have ;
  Error_record=cats(FLT_LAYR_NM,'|',FYP_NM,'|',_TEAM_NM);

  Error_id=1001;
  if missing(FYP_NM) then output;

  Error_id=1002;
  if _TEAM_NM not in ('Business','Employee') then output;
run;
  
    
proc print;
run;

Obs    Error_id    Error_record

 1       1002      DID|Rangecheck|Client
 2       1001      DID||Client
 3       1002      DID||Client

[David_Billa]

Right. I'm looking for alternate ways as well.

[David_Billa]

I ran the code below, but it is producing all the records in Output dataset instead of the records which qualified the below condition. May I know what I'm doing wrong here?

data OPER_FLT;
	length FLT_ID 8	FLT_REC_TXT $200;
	keep FLT_ID	FLT_REC_TXT;
	set insurance_copy;
	FLT_REC_TXT=cats(SOURCE_SYSTEM_CD,'|',FILE_ID,'|',Delivery_TST); /*fields names will be differ for each table*/

    FLT_ID=600;
	if missing(CASHFLOW_AMT) then
		output;

	FLT_ID=525;
	if missing(CASHFLOW_DT) then
		output;

	FLT_ID=645;
	if CED_FLG not in ("Y","N") then
		output;

	FLT_ID=712;
	if SCENARIO_ID ne “BASECASE” then
		output;
run;

part of log:

NOTE: Variable '“BASECASE”'n is uninitialized.
NOTE: There were 14420 observations read from the data set WORK.INSURANCE_CASHFLOW_COPY.
NOTE: The data set WORK.OPER_FLT_LOG_REC has 14420 observations and 2 variables.
NOTE: DATA statement used (Total process time):

[andreas_lds]

There are fancy quotes in your code, check the line where you are comparing with "BASECASE".

It is highly recommended to turn of the option replacing quotes in your office application, see https://support.office.com/en-us/article/Change-curly-quotes-to-straight-quotes-and-vice-versa-01796...

[David_Billa]

Could you please tell me how the variable 'Error_id' is conditionally created before processing the if clause?

[Tom]

---

@David_Billa wrote:

Could you please tell me how the variable 'Error_id' is conditionally created before processing the if clause?

---

It wasn't.

What was conditional was whether a record was written to the output file.  When you write the record it takes the current value of the variables.