Identifying data with character variable

[priya1286]

Hi,

I am working with the following data:

Obs    line1_regimen

0013  adali certoli hcq

001a  certoli hcq etaner tofa goli

0034  adali inflix lef mtx

0054  adali hcq inflix mtx

Obs is the unique identifier, and 'line1_regimen' is the variable name. There are about 170000 observations in my data with different combinations of 'adali', 'certoli' etc. Max number of drugs in the variable is 6. I want to identify each of the combinations, however, if the variable line1_regimen has more than 2 drug names then I want to separate them into another dataset.

Currently, I am typing in these combinations individually into the if-then command. Please let me know if there is an efficient way to do this.

Thanks!

Cheers,

Priya

[KachiM]

Can you show what are the 2 output data sets you need based on the data you have shown here?

[RW9]

Show your required output, we can't guess.  Also please post test data in the form of a datastep so we have something to run with.  At a guess from what you posted:

data want (keep=regimen);
  set have;
  length regimen $2000;
  do i=1 to countw(line1_regimen," ");
    regimen=scan(line1_regimen,i," ");
    output;
  end;
run;
proc sort data=want nodupkey;
  by regimen;
run;

[Astounding]

Separating the data sets is easy:

data two many;

set have;

if countw(line1_regimen) > 2 then output many;

else output two;

run;

To find combinations, important questions needs to be answered to determine a good way to proceed.

How many different drug names might appear in the entire data set?

Are all the drug names just a single word or could a single drug name be two words?

Do you need to identify all the patients who took "adali", all who took "certoli", etc.?

What other variables are part of the incoming data?

[priya1286]

I separated the datasets and entered in combinations. I think there were just too many variations to account for. But thank you for all the suggestions. They were very helpful!

[s_lassen]

How many drugs have you got? If not too many, you can use a binary variable to mark each combination, e.g.

data have;
infile cards truncover;
input id $4. line1_regimen $char80.;
cards;
0013 adali certoli hcq
001a certoli hcq etaner tofa goli
0034 adali inflix lef mtx
0054 adali hcq inflix mtx
;run;

data drugs;
  set have;
  length drug $10;
  do _N_=1 to countw(line1_regimen,' ');
    drug=scan(line1_regimen,_N_,' ');
    output;
    end;
  keep drug;
run;

proc sql noprint; /* put quoted drug names in macro variable */
  select distinct quote(strip(drug),'''') into :drugs separated by ' ' from drugs;
  %let noof_drugs=&sqlobs;    /* no. of drugs */
quit;



data simple complex;
  set have;
  length comb $&noof_drugs;
  comb=repeat('0',&noof_drugs-1);
  array drugs(&noof_drugs) $10 _temporary_ (&drugs);
  do _N_=1 to countw(line1_regimen,' ');
    substr(comb,whichc(scan(line1_regimen,_N_,' '),of drugs(*)),1)='1';
    end;
  if length(compress(comb,'0'))>2 then output complex;
  else output simple;
run;

COMB will then mark which unique drugs are present in each regimen. ('1' in position 1 means that drug no. 1 is present, '0' means that it is not, etc.)