Skip to content
Advertisement

SAS computing multiple new variables from one row

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement