Need the compliment of the following code

[montgomerybarre]

I have the following code which captures 105 of 1400 possible diagnoses: 

proc sql;
	select DISTINCT quote(trim(dsc_diag_1)) INTO :BH_LIST separated by ' , '
	from claims1
	where (dsc_diag_1 like '% ANXIETY %' OR dsc_diag_1 like '% DEMENTIA %' OR dsc_diag_1 like '% MOOD %' OR dsc_diag_1 like '% SCHIZOPHRENIA %' OR dsc_diag_1 like '%SCHIZO%'
	OR dsc_diag_1 like '% DISSOCIATIVE %' OR dsc_diag_1 like '% STRESS %' OR dsc_diag_1 like '% DELUSIONAL %' OR dsc_diag_1 like '% PSYCHOACTIVE %'
	OR dsc_diag_1 like '% BEHAVIORAL %' OR dsc_diag_1 like '% MENTAL %' OR dsc_diag_1 like '% PERSONALITY %' OR dsc_diag_1 like '% INTELLECTUAL %'
	OR dsc_diag_1 like '% DEVELOPMENTAL %' OR dsc_diag_1 like '% EMOTIONAL %' OR dsc_diag_1 like '% PSYCH %' OR dsc_diag_1 like '% ALCOHOL %' 
	OR dsc_diag_1 like '% DEPRESSIVE %' OR dsc_diag_1 like '% DEPRESSION %' OR dsc_diag_1 like '% NEURO %' OR dsc_diag_1 like '% DELUSION %' 
	OR dsc_diag_1 like '% CANNABIS ABUSE%' OR dsc_diag_1 like '% CANNABIS USE%' OR dsc_diag_1 like '% CANNABIS DEPENDENCE%' 
	OR dsc_diag_1 like '% SEDATIVE, %' OR dsc_diag_1 like '% COCAINE %' OR dsc_diag_1 like '% NICOTINE %' 
	OR dsc_diag_1 like '% INHALANT %' OR dsc_diag_1 like '% STIMULANT %' OR dsc_diag_1 like '% SCHIZOAFFECTIVE %' OR dsc_diag_1 like '% PSYCHOSIS %'
	OR dsc_diag_1 like '% BIPOLAR %' OR dsc_diag_1 like '% DEPRESS %' OR dsc_diag_1 like '% CYCLOTHYMIC %' OR dsc_diag_1 like '% DYSTHIMIC %'
	OR dsc_diag_1 like '% PANIC %' OR dsc_diag_1 like '% ADJUSTMENT DISORDER %' OR dsc_diag_1 like '% POST-TRAUMATIC STRESS %' OR dsc_diag_1 like '% SOMATOFORM %'
	OR dsc_diag_1 like '% ANTISOCIAL %' OR dsc_diag_1 like '% BORDERLINE PERSONALITY %' OR dsc_diag_1 like '% EXPLOSIVE %' OR dsc_diag_1 like '% IMPULSE %'
	OR dsc_diag_1 like '% HYPERACTIVITY %' OR dsc_diag_1 like '% CONDUCT %' OR dsc_diag_1 like '% BEHAV/EMOTN %' OR dsc_diag_1 like '% IMPULSE %')
	and dsc_diag_1 not like '% OPIOID %' and dsc_diag_1 not like '% REMISSION %';
quit;

I am having trouble for some reason creating the compliment of this code, which would create a list of the other 1295 diagnoses codes. I cannot tell if my code is working, and the macro list is too long so the code stops running, or if my code is wrong altogether. I have tried a few variations on the following:

proc sql;
	select DISTINCT quote(trim(dsc_diag_1)) INTO :PH_LIST separated by ' , '
	from claims1
	where dsc_diag_1 not like '% ANXIETY %' and dsc_diag_1 not like '% DEMENTIA %' and dsc_diag_1 not like '% MOOD %' and dsc_diag_1 not like '% SCHIZOPHRENIA %' 
	or dsc_diag_1 not like '% SCHIZO %' and dsc_diag_1 not like '% SEDATIVE, %' and dsc_diag_1 not like '% COCAINE %' and dsc_diag_1 not like '% NICOTINE %' 
	or dsc_diag_1 not like '% DISSOCIATIVE %' and dsc_diag_1 not like '% DELUSIONAL %' and dsc_diag_1 not like '% PSYCHOACTIVE %'
	or dsc_diag_1 not like '% BEHAVIORAL %' and dsc_diag_1 not like '% MENTAL %' and dsc_diag_1 not like '% PERSONALITY %' and dsc_diag_1 not like '% INTELLECTUAL %'
	or dsc_diag_1 not like '% DEVELOPMENTAL %' and dsc_diag_1 not like '% EMOTIONAL %' and dsc_diag_1 not like '% PSYCH %' and dsc_diag_1 not like '% ALCOHOL %' 
	or dsc_diag_1 not like '% DEPRESSIVE %' and dsc_diag_1 not like '% DEPRESSION %' and dsc_diag_1 not like '% NEURO %' and dsc_diag_1 not like '% DELUSION %' 
	or dsc_diag_1 not like '% CANNABIS ABUSE%' and dsc_diag_1 not like '% CANNABIS USE%' and dsc_diag_1 not like '% CANNABIS DEPENDENCE %' 
	or dsc_diag_1 not like '% INHALANT %' and dsc_diag_1 not like '% STIMULANT %' and dsc_diag_1 not like '% SCHIZOAFFECTIVE %' and dsc_diag_1 not like '% PSYCHOSIS %'
	or dsc_diag_1 not like '% BIPOLAR %' and dsc_diag_1 not like '% DEPRESS %' and dsc_diag_1 not like '% CYCLOTHYMIC %' and dsc_diag_1 not like '% DYSTHIMIC %'
	or dsc_diag_1 not like '% PANIC %' and dsc_diag_1 not like '% ADJUSTMENT DISORDER %' and dsc_diag_1 not like '% POST-TRAUMATIC STRESS %' and dsc_diag_1 not like '% SOMATOFORM %'
	or dsc_diag_1 not like '% ANTISOCIAL %' and dsc_diag_1 not like '% BORDERLINE PERSONALITY %' and dsc_diag_1 not like '% EXPLOSIVE %' and dsc_diag_1 not like '% IMPULSE %'
	or dsc_diag_1 not like '% HYPERACTIVITY %' and dsc_diag_1 not like '% CONDUCT %' and dsc_diag_1 not like '% BEHAV/EMOTN %' and dsc_diag_1 not like '% IMPULSE %'
	or dsc_diag_1 not like '% OPIOID %' and dsc_diag_1 not like '% REMISSION %';
quit;

Thank you for your time,

Barrett

[mkeintz · Accepted solution]

I think you can put parentheses around the entire where expression, and prefix it with NOT, as in:

proc sql;
	select DISTINCT quote(trim(dsc_diag_1)) INTO :BH_LIST separated by ' , '
	from claims1
	where  NOT (
 (dsc_diag_1 like '% ANXIETY %' OR dsc_diag_1 like '% DEMENTIA %' OR dsc_diag_1 like '% MOOD %' OR dsc_diag_1 like '% SCHIZOPHRENIA %' OR dsc_diag_1 like '%SCHIZO%'
	OR dsc_diag_1 like '% DISSOCIATIVE %' OR dsc_diag_1 like '% STRESS %' OR dsc_diag_1 like '% DELUSIONAL %' OR dsc_diag_1 like '% PSYCHOACTIVE %'
	OR dsc_diag_1 like '% BEHAVIORAL %' OR dsc_diag_1 like '% MENTAL %' OR dsc_diag_1 like '% PERSONALITY %' OR dsc_diag_1 like '% INTELLECTUAL %'
	OR dsc_diag_1 like '% DEVELOPMENTAL %' OR dsc_diag_1 like '% EMOTIONAL %' OR dsc_diag_1 like '% PSYCH %' OR dsc_diag_1 like '% ALCOHOL %' 
	OR dsc_diag_1 like '% DEPRESSIVE %' OR dsc_diag_1 like '% DEPRESSION %' OR dsc_diag_1 like '% NEURO %' OR dsc_diag_1 like '% DELUSION %' 
	OR dsc_diag_1 like '% CANNABIS ABUSE%' OR dsc_diag_1 like '% CANNABIS USE%' OR dsc_diag_1 like '% CANNABIS DEPENDENCE%' 
	OR dsc_diag_1 like '% SEDATIVE, %' OR dsc_diag_1 like '% COCAINE %' OR dsc_diag_1 like '% NICOTINE %' 
	OR dsc_diag_1 like '% INHALANT %' OR dsc_diag_1 like '% STIMULANT %' OR dsc_diag_1 like '% SCHIZOAFFECTIVE %' OR dsc_diag_1 like '% PSYCHOSIS %'
	OR dsc_diag_1 like '% BIPOLAR %' OR dsc_diag_1 like '% DEPRESS %' OR dsc_diag_1 like '% CYCLOTHYMIC %' OR dsc_diag_1 like '% DYSTHIMIC %'
	OR dsc_diag_1 like '% PANIC %' OR dsc_diag_1 like '% ADJUSTMENT DISORDER %' OR dsc_diag_1 like '% POST-TRAUMATIC STRESS %' OR dsc_diag_1 like '% SOMATOFORM %'
	OR dsc_diag_1 like '% ANTISOCIAL %' OR dsc_diag_1 like '% BORDERLINE PERSONALITY %' OR dsc_diag_1 like '% EXPLOSIVE %' OR dsc_diag_1 like '% IMPULSE %'
	OR dsc_diag_1 like '% HYPERACTIVITY %' OR dsc_diag_1 like '% CONDUCT %' OR dsc_diag_1 like '% BEHAV/EMOTN %' OR dsc_diag_1 like '% IMPULSE %')
	and dsc_diag_1 not like '% OPIOID %' and dsc_diag_1 not like '% REMISSION %'
  ) ;
quit;

[RichardDeVen]

The maximum length of a macro variable value is 64K. 

The LOG will indicate when the limit is exceeded by an INTO :PH_LIST clause

Example log

ERROR: The length of the value of the macro variable PH_LIST (65540) exceeds the maximum length
       (65534). The value has been truncated to 65534 characters.

The bigger issue is the miscoded converse classification logic

- missing parenthesis

- some internal OR's not changed to AND's

- external AND's not changed to OR's

Conceptually, the logic statement (BH) and it's compliment (PH) are 

BH: ( A  or  B  or  C) and ~D and ~E

PH: (~A and ~B and ~C)  or  D  or  E  (i.e. logical compliment is negation)

The BH where statement

where dsc_diag_1 not like '% ANXIETY %' and dsc_diag_1 not like '% DEMENTIA %' and dsc_diag_1 not like '% MOOD %' and dsc_diag_1 not like '% SCHIZOPHRENIA %' 
	or dsc_diag_1 not like '% SCHIZO %' and dsc_diag_1 not like '% SEDATIVE, %' and dsc_diag_1 not like '% COCAINE %' and dsc_diag_1 not like '% NICOTINE %' 
	or dsc_diag_1 not like '% DISSOCIATIVE %' and dsc_diag_1 not like '% DELUSIONAL %' and dsc_diag_1 not like '% PSYCHOACTIVE %'
        or ...
        or dsc_diag_1 not like '% OPIOID %' and dsc_diag_1 not like '% REMISSION %'

Should be this ~BH statement

where (
      dsc_diag_1 not like '% ANXIETY %' and dsc_diag_1 not like '% DEMENTIA %' and dsc_diag_1 not like '% MOOD %' and dsc_diag_1 not like '% SCHIZOPHRENIA %' 
AND dsc_diag_1 not like '% SCHIZO %' and dsc_diag_1 not like '% SEDATIVE, %' and dsc_diag_1 not like '% COCAINE %' and dsc_diag_1 not like '% NICOTINE %' 
AND dsc_diag_1 not like '% DISSOCIATIVE %' and dsc_diag_1 not like '% DELUSIONAL %' and dsc_diag_1 not like '% PSYCHOACTIVE %'
AND ...
      )
        or dsc_diag_1 like '% OPIOID %' or dsc_diag_1 like '% REMISSION %'

[Shmuel]

Try create both lists by next code:

proc sql noprint;
     create table BH_list as 
     select DISTINCT quote(trim(dsc_diag_1)) as __diag
               from claims where(  ... )  ;  /* capture the 105 diagnoses */
			   
     create table PH_list as 
      select DISTINCT quote(trim(dsc_diag_1)) as __diag
              from claims where __diag not in
               (select __diag  from BH_list);
			   
     select __diag into :BH_list separated by ' , ' from BH_list;
     select __diag into :PH_list separated by ' , ' from PH_list;
quit;

[ChrisNZ]

I could compliment your code if you really insist, but I think you run the risk to exceed the maximum macro variable length.

It might be better to create a table with the values you want, and use it as a look up.

Also you could use the contain operator, or its shortcut, for more compact code:

where (DSC_DIAG_1 ? ' ANXIETY ' | DSC_DIAG_1 ? ' DEMENTIA '  ...

[Tom]

You need to apply DeMorgans law. in both directions.

Your overall structure is 

A and B and C

Which you can convert using 

not (A and X and Y) = (not A) or (not X) or (not Y)

The first part, A, is in the form

B or C or D ...

Which you can convert using

not (B or C or D ) = (not B) and (not C) and (not D)

So the compliment of 

(B or C or D) and X and Y

Is 

((not B) and (not C) and (not D)) or (not X) or (not Y)

But why not just save a lot of time and just add NOT ( ) around the existing code?

[Kurt_Bremser]

Keep the diagnoses in a dataset, and create the code dynamically from there with call execute.

[Patrick]

@montgomerybarre 

It feels that creating a lookup table instead of macro variables is eventually the better option.

Why do you create this macro variable? What do you want to do with it?

[montgomerybarre]

Hi @Patrick and @Kurt_Bremser,

While everyone's comments about the logic were spot on, I've determined that logic is not the main issue. I have run different versions of the logic and I always get the same error about the macro variable truncation: ERROR: The length of the value of the macro variable BH_LIST (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters.

The output in the log and results made me thing that an error in the logic was causing an overly-long macro list, but I've determined that even the correct list will be longer than a macro list can contain. 

@Kurt_Bremser could you direct me towards the statements or code that would create a data set from the proc sql code? I think you are right that this is the only way to move forward, but I've never done this before.

@Patrick Agreed, I need to make a lookup table. I will use both of these sets of diagnosis codes in order to create counts of how many unique diagnoses each individual in my data set has been diagnosed with. I have already done this successfully with the BH_List, but the larger PH_list is presenting problems due to its size.

[Patrick]

---

@montgomerybarre wrote:

....

@Patrick Agreed, I need to make a lookup table. I will use both of these sets of diagnosis codes in order to create counts of how many unique diagnoses each individual in my data set has been diagnosed with. I have already done this successfully with the BH_List, but the larger PH_list is presenting problems due to its size.

---

I assume you don't have the DSM or ICD codes in your data and that's why you're using the diagnosis text. So once you've pulled out the diagnosis as per your selection how do you plan to count? By individual text or by some grouping? Looking at your selections there are variations in your diagnosis text. So for the case below would you want to have the two strings in the same group or would you want to count them as different groups?

OR dsc_diag_1 like '% CANNABIS ABUSE%' OR dsc_diag_1 like '% CANNABIS USE%'

What about creating a grouping variable first? You then can do any further selection or grouping using this grouping variable.

What data volumes are you dealing with? How many rows does your biggest table have?.

[montgomerybarre]

Yes we don't have a complete list of those codes which is why I need to comb through all the data to create our own categorizations. The end goal here is to create binary variables that capture whether the individual has ever been diagnosed with a behavioral health diagnosis (that first list of like % dx % you see) or a physical health diagnosis (will be the complementary set of diagnoses), and then also two continuous variables that count the number of unique behavioral health diagnoses and psychical health diagnoses. I used the following code to accomplish this for the diagnoses that fall into the BH_list macro list (there are 24 diagnosis variables total, hence the array):

title3'Check for BH related dx';
proc sql;
	select DISTINCT quote(trim(dsc_diag_1)) INTO :BH_LIST separated by ' , '
	from claims1
	where (dsc_diag_1 like '% ANXIETY %' OR dsc_diag_1 like '% DEMENTIA %' OR dsc_diag_1 like '% MOOD %' OR dsc_diag_1 like '% SCHIZOPHRENIA %' OR dsc_diag_1 like '%SCHIZO%'
	OR dsc_diag_1 like '% DISSOCIATIVE %' OR dsc_diag_1 like '% STRESS %' OR dsc_diag_1 like '% DELUSIONAL %' OR dsc_diag_1 like '% PSYCHOACTIVE %'
	OR dsc_diag_1 like '% BEHAVIORAL %' OR dsc_diag_1 like '% MENTAL %' OR dsc_diag_1 like '% PERSONALITY %' OR dsc_diag_1 like '% INTELLECTUAL %'
	OR dsc_diag_1 like '% DEVELOPMENTAL %' OR dsc_diag_1 like '% EMOTIONAL %' OR dsc_diag_1 like '% PSYCH %' OR dsc_diag_1 like '% ALCOHOL %' 
	OR dsc_diag_1 like '% DEPRESSIVE %' OR dsc_diag_1 like '% DEPRESSION %' OR dsc_diag_1 like '% NEURO %' OR dsc_diag_1 like '% DELUSION %' 
	OR dsc_diag_1 like '% CANNABIS ABUSE%' OR dsc_diag_1 like '% CANNABIS USE%' OR dsc_diag_1 like '% CANNABIS DEPENDENCE%' 
	OR dsc_diag_1 like '% SEDATIVE, %' OR dsc_diag_1 like '% COCAINE %' OR dsc_diag_1 like '% NICOTINE %' 
	OR dsc_diag_1 like '% INHALANT %' OR dsc_diag_1 like '% STIMULANT %' OR dsc_diag_1 like '% SCHIZOAFFECTIVE %' OR dsc_diag_1 like '% PSYCHOSIS %'
	OR dsc_diag_1 like '% BIPOLAR %' OR dsc_diag_1 like '% DEPRESS %' OR dsc_diag_1 like '% CYCLOTHYMIC %' OR dsc_diag_1 like '% DYSTHIMIC %'
	OR dsc_diag_1 like '% PANIC %' OR dsc_diag_1 like '% ADJUSTMENT DISORDER %' OR dsc_diag_1 like '% POST-TRAUMATIC STRESS %' OR dsc_diag_1 like '% SOMATOFORM %'
	OR dsc_diag_1 like '% ANTISOCIAL %' OR dsc_diag_1 like '% BORDERLINE PERSONALITY %' OR dsc_diag_1 like '% EXPLOSIVE %' OR dsc_diag_1 like '% IMPULSE %'
	OR dsc_diag_1 like '% HYPERACTIVITY %' OR dsc_diag_1 like '% CONDUCT %' OR dsc_diag_1 like '% BEHAV/EMOTN %' OR dsc_diag_1 like '% IMPULSE %')
	and dsc_diag_1 not like '% OPIOID %' and dsc_diag_1 not like '% REMISSION %';
quit;

data long;
   set claims1;
   array d dsc_diag_: ;
   length onediag $ 200;
   do i=1 to dim(d);
      if not missing(d[i]) then do;
         onediag=d[i];
         output;
      end;
   end;
   keep study_id onediag;
run;

proc sql;
   create table dcount as
   select study_id, count(*) as bh_complexity
   from (select distinct study_id,onediag
          from long)
   where onediag in (&BH_LIST)
   group by study_id ;
quit;

data claims2;
merge claims1 dcount;
by study_id;
if bh_complexity = . then bh_complexity = 0;
label bh_complexity = 'count of beahvioral health diagnoses (opioids and remission dx excluded) per probationer';
run;

[ChrisNZ]

1. Your list is impossible to read and maintain. So much so that it contains duplicates (IMPULSE is repeated, and BORDERLINE PERSONALITY is already flagged by PERSONALITY) 

and the spacing is not always consistent (no space around SCHIZO) and that's impossible to see.

You should write it differently so it can more easily be vetted. See below. 

If you write as below, you can also flag the individuals who have been diagnosed with a behavioural health issues in one single step.

data CLAIMS2; 
  LIST = ' ADJUSTMENT DISORDER | ALCOHOL  ANTISOCIAL | ANXIETY' 
    || ' | BEHAVIORAL | BEHAV/EMOTN| BIPOLAR | BORDERLINE PERSONALITY' 
    || ' | CANNABIS ABUSE| CANNABIS USE| CANNABIS DEPENDENCE| COCAINE | CONDUCT | CYCLOTHYMIC' 
    || ' | DELUSION | DELUSIONAL | DEMENTIA | DEPRESS | DEPRESSIVE | DEPRESSION | DEVELOPMENTAL | DISSOCIATIVE | DYSTHIMIC' 
    || ' | EMOTIONAL | EXPLOSIVE' 
    || ' | HYPERACTIVITY'
    || ' | IMPULSE | INHALANT | INTELLECTUAL'
    || ' | MENTAL | MOOD' 
    || ' | NEURO | NICOTINE'
    || ' | PANIC | PERSONALITY | POST-TRAUMATIC STRESS | PSYCHOACTIVE | PSYCH | PSYCHOSIS' 
    || ' |SCHIZO| SCHIZOAFFECTIVE | SEDATIVE | SOMATOFORM | STIMULANT | STRESS ';
  set CLAIMS1;
  array D DSC_DIAG_: ;
  do I=1 to dim(D);
    if prxmatch('m/'||LIST||'/', D[I]) & ^index(' OPIOID ',D[I]) & ^index(' REMISSION ',D[I]) then MENTAL_FLAG=1;
  end;
run;

[Kurt_Bremser]

---

@montgomerybarre wrote:

@Kurt_Bremser could you direct me towards the statements or code that would create a data set from the proc sql code? I think you are right that this is the only way to move forward, but I've never done this before.

---

Say you have this (abbreviated) SQL code:

proc sql;
create table want as
  select *
  from have
  where
    dsc_diag_1 like '% ANXIETY %'
    or dsc_diag_1 like '% DEMENTIA %'
    or dsc_diag_1 like '% MOOD %'
    or dsc_diag_1 like '% SCHIZOPHRENIA %'
    or dsc_diag_1 like '%SCHIZO%'
    or dsc_diag_1 like '% DISSOCIATIVE %'
;
quit;

Put all those codes into a dataset, which is unlimited in size for all purposes:

data lookup;
input cond_value $50.;
datalines;
% ANXIETY %
% DEMENTIA %
% MOOD %
% SCHIZOPHRENIA %
%SCHIZO%
% DISSOCIATIVE %
;

Now you create the previous SQL step dynamically:

data _null_;
set lookup end=done;
if _n_ = 1 then call execute("
proc sql;
create table want as
  select *
  from have
  where
");
if _n_ > 1 then call execute(" or");
call execute(" dsc_diag_1 like '" !! strip(cond_value) !! "'");
if done then call execute("
;
quit;
");
run;

You can see how the original SQL step code is reflected in the data _null_ step for easier reading and maintenance.