topic Re: Identifying data with character variable in SAS Programming

Identifying data with character variable

priya1286 — Mon, 02 Oct 2017 12:48:10 GMT

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

Re: Identifying data with character variable

KachiM — Mon, 02 Oct 2017 13:12:36 GMT

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

Re: Identifying data with character variable

RW9 — Mon, 02 Oct 2017 13:52:57 GMT

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;

Re: Identifying data with character variable

Astounding — Mon, 02 Oct 2017 13:56:05 GMT

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?

Re: Identifying data with character variable

priya1286 — Mon, 04 Dec 2017 15:53:04 GMT

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!

Re: Identifying data with character variable

s_lassen — Tue, 05 Dec 2017 09:03:20 GMT

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.)