Jan's Blog

Arrays in SAS are incredibly useful things. For example, if you’re dealing with a concomitant medications dataset and want to check for certain medicines across multiple columns, you’d be hard-pressed to find a faster method than using arrays!

Within the SDTM.CM domain, medication names are spread across several columns, usually: CMTRT (Reported Name of Drug, Med, or Therapy), CMMODIFY (Modified Reported Name), and CMDECOD (Standardized Medication Name).

If we wanted to find and flag, for example, the following three medicines: Aspirin, Antacid, Potassium Chloride ; we could do it as follows:

data cm;
    set sdtm.cm;

    array all_meds {*} $ cmtrt cmmodify cmdecod;
    array check_meds {3} $ _temporary_ ("aspirin", "antacid", "potassium chloride");

    do i = 1 to dim(all_meds);
        do j = 1 to dim(check_meds);
            if upcase(all_meds[i]) = upcase(check_meds[j]) then occur = "Y";
        end;
    end;

    if occur ^= "Y" then delete;
run;

The above method works well to quickly check for multiple conmeds across multiple columns. However, usually a conmeds list is not restricted to only 3 items and counting the number of unique items is a slow and tedious process. If an item needs to be added or removed, then the space to be reserved needs to be updated.

Unfortunately specifying the list of conmeds in a temporary array prevents the use of the dynamic sizing, usually indicated by {*}. This is something that will hopefully be fixed in a newer version of SAS, but until such time I’ve taken it upon myself to create a macro which creates dynamically sized (sort of!) temporary arrays. It does this by counting the number of items and automatically reserving the space for it.

Note: if you are feeling lazy, you can still create a temporary array and simply oversize it, e.g., specify a size of 100 items even though you may only need half that. However, this will result in SAS posting a WARNING to the log about partial array initialization, which is not ideal in the pharmaceutical environment where we want clean logs!

If you’d prefer to avoid another O(n) loop which checks for the maximum length needed for character variables, simply set a static length, say $20.

/*
    Macro makeTempArray
    
    Purpose: To create temporary arrays without knowing the
             size of the array needed beforehand. This is a 
             limitation of the original SAS procedure for
             creating temporary arrays.
    
    Parameters:
        arrayname : an arbitrary name for your array
        ischar    : pass either Y for a character array 
                    or N for numeric
        items     : pass the list of items to be contained in
                    the array, wrapped in %str() and separated
                    by commas
*/

%macro makeTempArray(arrayname=, ischar=, items=);
    %let n=%sysfunc(countw(&items., %str(,), )); /*count the number of items to reserve space for*/
   
    %if &ischar.=Y %then %do; /*if this is a character array, we need the length of the longest item*/ 
        %let l = 1;
        %do j = 1 %to &n.;
            %let l0 = %sysfunc(length(%sysfunc(scan(&items., &j., %str(,), r))));
            %if  &l0. > &l. %then %let l = &l0.;
        %end;
    %end;
    
    array &arrayname. {&n.} %if &ischar.=Y %then $&l.; _temporary_ (
        %do i = 1 %to &n.;
            %let item = %sysfunc(scan(&items., &i., %str(,), r));
            %if &ischar.=Y %then %str("&item." ); %else &item.;
        %end;
    );
%mend makeTempArray;

With this macro, we can now modify our initial starting block of code as follows (items must be separated by commas):

data cm;
    set sdtm.cm;

    array all_meds {*} $ cmtrt cmmodify cmdecod;
    %makeTempArray(arrayname=%str(check_meds), ischar=%str(Y), items=%str(aspirin, antacid, potassium chloride));

    do i = 1 to dim(all_meds);
        do j = 1 to dim(check_meds);
            if upcase(all_meds[i]) = upcase(check_meds[j]) then occur = "Y";
        end;

        if occur = "Y" then leave;
    end;

    if occur ^= "Y" then delete;
run;

Hope this helps you next time you need to cross-check multiple items across multiple columns! Happy hacking!

Updates: My colleague, Mazi Ntintelo has rightly pointed out that the commas within the macro’s scan functions should be wrapped as %str(,) and also that the do loop checking for conmeds can be optimised with a leave statement. Thanks, Mazi!