Arrays

[Ranjeeta]

proc sql;
create table DAD_CABG as
select CIHI_KEY, INST_NO, Gender, Age, POSTAL_CODE, PATIENT_LHIN, Residence_Code,
ADM_DT, ADM_TM, Inst_From_NO, Facility_NO, FACILITY_LHIN, FACILITY_NAME,Adm_category, MCC, MCC_PARTITION,ACUTE_LENGTH_OF_STAY,TOTAL_LENGTH_OF_STAY,HIG_CODE,HIG_WEIGHT,
Diag_code_01, Diag_code_02, Diag_code_03, Diag_code_04, Diag_code_05,
Diag_code_06, Diag_code_07, Diag_code_08, Diag_code_09, Diag_code_10,
Diag_code_11, Diag_code_12, Diag_code_13, Diag_code_14, Diag_code_15,
Diag_code_16, Diag_code_17, Diag_code_18, Diag_code_19, Diag_code_20,
Diag_code_21, Diag_code_22, Diag_code_23, Diag_code_24, Diag_code_25,
Diag_type_01, Diag_type_02, Diag_type_03, Diag_type_04, Diag_type_05,
Diag_type_06, Diag_type_07, Diag_type_08, Diag_type_09, Diag_type_10,
Diag_type_11, Diag_type_12, Diag_type_13, Diag_type_14, Diag_type_15,
Diag_type_16, Diag_type_17, Diag_type_18, Diag_type_19, Diag_type_20,
Diag_type_21, Diag_type_22, Diag_type_23, Diag_type_24, Diag_type_25,
Interv_code_01, Interv_code_02, Interv_code_03, Interv_code_04, Interv_code_05, Interv_code_06,
Interv_code_07, Interv_code_08, Interv_code_09, Interv_code_10, Interv_code_11, Interv_code_12,
Interv_code_13, Interv_code_14, Interv_code_15, Interv_code_16, Interv_code_17, Interv_code_18,
Interv_code_19, Interv_code_20,
INTERV_OOH_IND_01, INTERV_OOH_IND_02, INTERV_OOH_IND_03, INTERV_OOH_IND_04, INTERV_OOH_IND_05, INTERV_OOH_IND_06,
INTERV_OOH_IND_07, INTERV_OOH_IND_08, INTERV_OOH_IND_09, INTERV_OOH_IND_10, INTERV_OOH_IND_11, INTERV_OOH_IND_12,
INTERV_OOH_IND_13, INTERV_OOH_IND_14, INTERV_OOH_IND_15, INTERV_OOH_IND_16, INTERV_OOH_IND_17, INTERV_OOH_IND_18,
INTERV_OOH_IND_19, INTERV_OOH_IND_20,
Interv_status_attrib_01, Interv_status_attrib_02, Interv_status_attrib_03, Interv_status_attrib_04, Interv_status_attrib_05,
Interv_status_attrib_06, Interv_status_attrib_07, Interv_status_attrib_08, Interv_status_attrib_09, Interv_status_attrib_10,
Interv_status_attrib_11, Interv_status_attrib_12, Interv_status_attrib_13, Interv_status_attrib_14, Interv_status_attrib_15,
Interv_status_attrib_16, Interv_status_attrib_17, Interv_status_attrib_18, Interv_status_attrib_19, Interv_status_attrib_20,
HCNE
from DAD.DAD1617
where PROVINCE_ISSUING_HCN='ON' and RESPONSIBILITY_FOR_PAYMENT='01' and HCN_INDEX='H'
and AGE >=18
and INTERV_CODE_01 in ('1IJ76DAXX', '1IJ76LAXX', '1IJ76LAXXA', '1IJ76LAXXG','1IJ76LAXXN','1IJ76LAXXQ');

quit;

How would I modify the above code to select cases such that the bold highlighted above are present in either of the 19 variables interv_code_02 to interv_code_20 and not in interv_code_01

[Kurt_Bremser]

Use a data step with arrays, or variable lists with ranges (interv_code01-interv_code20).

SQL does not have arrays, and since this is a straight read that makes no use of SQL-specific capabilities, a data step is the tool of choice.

[PaigeMiller]

---

@Ranjeeta wrote:

How would I modify the above code to select cases such that the bold highlighted above are present in either of the 19 variables interv_code_02 to interv_code_20 and not in interv_code_01

---

The WHICHC function can find if a value is present in an array of variables. https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p...

You'd probably have to test each code (e.g. 1IJ76DAXX) individually.

[RW9]

Use findw() and catx() functions.  Catx() to put all the columns into one string, findw to see if the text string is one of them:

and (
  findw(catx(",",of interv_code:),"1lJ76DAXX") or
  findw(catx(",",of interv_code:),"1lJ76LAXX") or
  findw(catx(",",of interv_code:),"1lJ76LAXXA") or
...
      )

However there is likely to be a simpler method using a dataset, as you could put your find strings into an array as well and then loop:

data want;
  set have;
  array strs{2} $200 ("1lJ76DAX","1lJ76LAXX");
  array ic{20} interv_code:;
  do j=1 to 2;
    do i=1 to 20;
      if ic{i}=strs{j} then found=1;
    end;
  end;
run;   

  It may even be simpler than that, however without any test data (in the form of a datastep) or required output can't say.

[Ranjeeta]

DATA CABG1718;
/* LOAD DAD1718 DATA */
SET DAD.DAD1718 (KEEP=CIHI_KEY HCNE PROVINCE_ISSUING_HCN RESPONSIBILITY_FOR_PAYMENT HCN_INDEX POSTAL_CODE FACILITY_NO AGE DIAG_: INTERV_: MCC_PARTITION);

/* ARRAY - INTERVENTION CODE */
ARRAY ARRAY_IC [19] INTERV_CODE_02 - INTERV_CODE_20;
/* ARRAY - INTERVENTION ATTRIBUTE STATUS */
ARRAY ARRAY_IA [20] INTERV_STATUS_ATTRIB_01 - INTERV_STATUS_ATTRIB_20;
/* ARRAY - INTERVENTION OUT-OF-HOSPITAL INDICATORS */
ARRAY ARRAY_OOH [20] INTERV_OOH_IND_01 - INTERV_OOH_IND_20;

/* INITIALIZE INDICATORS AND FLAGS */

CABG=0;
FLAG_UNDER18=0;
FLAG_NONON=0;
FLAG_MCC=0;
FLAG_ABAN_OOH=1;
FLAG_OTHER_HVD=0;
FLAG_NONPRINCIPAL=1;
FLAG_EX1=0;
FLAG_EX2=0;
FLAG_EX3=0;

IF PROVINCE_ISSUING_HCN ~='ON' OR RESPONSIBILITY_FOR_PAYMENT~='01' OR HCN_INDEX~='H' THEN FLAG_NONON=1;
IF MCC_PARTITION ~='I' THEN FLAG_MCC=1;

DO I=2 TO 20;
/* SEARCH FOR CABG IN INTERVENTION CODES */
IF SUBSTR(ARRAY_IC[I],1,3)='1IJ' THEN DO;
CABG=1;
/* CHECK FOR AT LEAST 1 UN-ABANDONED CABG INTERVENTION */
IF ARRAY_IA[I] ~= 'A' THEN FLAG_ABANDONED=0;
/* CHECK FOR AT LEAST 1 NON OUT-OF-HOSPITAL CABG INTERVENTION */
IF ARRAY_OOH[I] ~= 'Y' THEN FLAG_OOH=0;

/* CREATE INDICATORS FOR SURGERY TYPES: CABG */
END;

/* SEARCH FOR UN-ABANDONED CABG IN INTERVENTION CODES */
IF SUBSTR(ARRAY_IC[I],1,5)='1IJ76' AND ARRAY_IA[I] ~= 'A' AND FLAG_UNDER18~=1 THEN CABG=1;

END; /* END OF DO-LOOP */

/* FLAG THOSE UNDER 18 */
IF AGE<18 THEN FLAG_UNDER18=1;


/* FLAG FOR ANY EXCLUSIONS */
IF SUM(FLAG_UNDER18, FLAG_NONON)>0 THEN FLAG_EX1=1;

LABEL CABG = 'Indicator for un-abandoned CABG surgery (1IJ76*)'
FLAG_UNDER18 = 'Flag for age under 18 years at time of admission'
FLAG_NONON = 'Flag for non-Ontario resident'
FLAG_MCC = "Flag for MCC partition is not 'I' (surgical)"
FLAG_ABANDONED = 'Flag for abandoned AVD operation'
FLAG_OOH = 'Flag for Out-of-Hospital interventions'
FLAG_NONPRINCIPAL = 'Flag for non-principal procedure'
FLAG_EX1 = 'Flag for under 18 or non-Ontario resident'

DROP I;
RUN;

I get the error that ERROR: Array subscript out of range at line 179 column 15

What does this mean?

[Kurt_Bremser]

Read the log. The line and column number point to the failing statement, and the listing of variable values will give you the state of the data step at the moment of the ERROR.

[ballardw]

The way you define the array and then reference the index causes the out of range error:

ARRAY ARRAY_IC [19] INTERV_CODE_02 - INTERV_CODE_20;
                                  ^
                                  19 elements
IDO I=2 TO 20;
/* SEARCH FOR CABG IN INTERVENTION CODES */
IF SUBSTR(ARRAY_IC[I],1,3)='1IJ' THEN DO;
                                       ^
                                      when I=20 you exceed the 19 elements

If you want to use 2 to 20 as the index values you need to do so explicitly in the definition of the array using the lower:upper such as

ARRAY ARRAY_IC (2:20} INTERV_CODE_02 - INTERV_CODE_20;

But since your other arrays have 20 elements and not 19 you need to think very carefully about which index value you want to use when referencing them. Since you use do I=2 to 20 then the first element of the other arrays.

Note that when your question is along the lines of "does value X appear in any of the array elements" you should look at the WHICHN and WHICHC functions

WHICHC('A', of ARRAY_IA[*]) will return the index of the first match if found or 0 if 'A' is not equal to any of the elements in the array.

I am not sure if this directly applies but may help depending on the actual values that are in your array(s).

[RW9]

Starting by not coding all in uppercase would help a lot.  Also, refer to what the log feedsback to you, as this is telling you what the issue is.  You haven't provided a log, but at line number in there, 179, you have an array statement which defines a certain number of elements.  The values you are assigning to it are more in number than the elements available.