topic Re: Using Column Values in an Array in SAS Programming

Using Column Values in an Array

jmmedina25 — Mon, 28 Nov 2022 14:46:30 GMT

Hi, I need to use the values of a column in one table as the list in an array. Currently I'm using macro variables to list the values I'm searching for, however one of my lists in in an imported excel doc with this format:

diag_cd (string variable)

E091

E307

E0072

This is my current code:

%let diag_cd0="E099", "E0910";

data notable_dx;
set claims;

   array diabarray {25} $ diag_cd1 - diag_cd25;
    diab_ind = 0;
    do i = 1 to 25;
        if diabarray {i} in: (&diag_cd0.)
        then diab_ind = 1;
    end;
    drop i;
run;

Is there a way to reference another tables values in the array or a way to transform the values in the first table to a list that looks like the macro variable?

Re: Using Column Values in an Array

PeterClemmensen — Tue, 29 Nov 2022 13:44:34 GMT

You could read the data set with the values in the first iteration of the data step, before your logic like this

data diag;
input diag_cd $;
datalines;
E091
E307
E0072
;

data notable_dx;

set claims;

array diabarray {25} $ diag_cd1 - diag_cd25;

if _N_ = 1 then do;
do idx = 1 by 1 until (z);
set diag end = z;
diabarray[idx] = diag_cd;
end;
end;

/* Your logic here */
run;

Re: Using Column Values in an Array

Tom — Mon, 28 Nov 2022 15:53:25 GMT

As long as the list of values is short enough that it can fit into a single macro variable then PROC SQL is the simplest way to transfer the values of a VARIABLE (what I assume you meant by the word column) in a dataset into a macro variable. So it looks like you have variable named DIAG_CD in some unknown dataset. Let's assume the dataset is named HAVE. This code will create a macro variable named DIAG_CD0 with space delimited list of quoted strings.

proc sql noprint;
select distinct quote(trim(diag_cd))
  into :diag_cd0 separated by ' '
from have
;
quit;

You can then use that macro variable later in your code:

data notable_dx;
  set claims;
  array diabarray diag_cd1 - diag_cd25;
  do i = 1 to dim(diabarray) until(diab_ind);
     diab_ind=  diabarray[i] in: (&diag_cd0.);
  end;
  drop i;
run;

Note that in SAS the IN operator allows you to use either spaces or commas to separate values in the list. Macro variables with commas in them are much harder to work with than macro variables with spaces.

Re: Using Column Values in an Array

jmmedina25 — Tue, 29 Nov 2022 12:56:54 GMT

The code is saying z is uninitialized.

Re: Using Column Values in an Array

PeterClemmensen — Tue, 29 Nov 2022 13:39:07 GMT

Sorry, just edited the code

Re: Using Column Values in an Array

andreas_lds — Tue, 29 Nov 2022 13:48:19 GMT

You could use a hash object (note: code is untested):

data diag;
   input diag_cd $;
   datalines;
E091
E307
E0072
;

data notable_dx;
   set claims;

   if _n_ = 1 then do;
      if 0 then set diag;
      declare hash h(dataset: 'diag');
      h.defineKey('diag_cd');
      h.defineDone();
   end;

   array diabarray {25} $ diag_cd1 - diag_cd25;

   diab_ind = 0;

   do i = 1 to 25;
      if h.check(key: diabarray[i]) = 0 then do;
         diab_ind = 1;
         leave; /* no need for further checks */
      end;
   end;

   drop i;
run;