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?
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
how come DID Rangecheck Employee should be in the Error Data?
Then why isn't DID|Rangecheck|Client in there?
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.
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.
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
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):
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...
Could you please tell me how the variable 'Error_id' is conditionally created before processing the if clause?
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.