I have a dataset as listed below:
ID-----V1-----V2------V3 01------5------3-------7 02------3------8-------5 03------6------9-------1
and I want to calculate 3 new variables (ERR_CODE, ERR_DETAIL, ERR_ID) according to behavior of certain columns.
- If V1 is greater than 4 then ERR_CODE = A and ERR_DETAIL = “Out of range” and ERR_ID = [ID]_A
- If V2 is greater than 4 then ERR_CODE = B and ERR_DETAIL = “Check Log” and ERR_ID = [ID]_B
- If V3 is greater than 4 then ERR_CODE = C and ERR_DETAIL = “Fault” and ERR_ID = [ID]_C
Desired output table be like
ID-----ERR_CODE----ERR_DETAIL---------ERR_ID 01--------A--------Out of range---------01_A 01--------C--------Fault----------------01_C 02--------B--------Check Log------------02_B 02--------C--------Fault----------------02_C 03--------A--------Out of range---------03_A 03--------B--------Check Log------------03_B
I am using SAS 9.3 with EG 5.1. I have tried do-loops, arrays, if statements and case-when’s but it naturally skips to the next row to calculate when condition is met. But i want to calculate other met conditions fo each row.
I have managed to do it by creating seperate tables for each condition and then merge them. But that doesn’t seem an effective way if there are much conditions to work with.
My question is how can i manage to calculate other met conditions for each ID at once without calculating seperately? The output table’s row count will be more than the input as expected but for me it is not possible to achieve by applying case-when or if etc.
Thanks in advance and sorry if i am not clear.
Advertisement
Answer
Just use IF/THEN/DO blocks. Add an OUTPUT statement to write new observation for each error.
data have ; input ID $ V1-V3; cards; 01 5 3 7 02 3 8 5 03 6 9 1 ; data want; set have; length ERR_CODE $1 ERR_DETAIL $20 ERR_ID $10 ; if v1>4 then do; err_code='A'; err_detail="Out of range"; err_id=catx('_',id,err_code); output; end; if v2>4 then do; err_code='B'; err_detail="Fault"; err_id=catx('_',id,err_code); output; end; if v3>4 then do; err_code='C'; err_detail="Check Log"; err_id=catx('_',id,err_code); output; end; drop v1-v3 ; run;
Results:
Obs ID ERR_CODE ERR_DETAIL ERR_ID 1 01 A Out of range 01_A 2 01 C Check Log 01_C 3 02 B Fault 02_B 4 02 C Check Log 02_C 5 03 A Out of range 03_A 6 03 B Fault 03_B