10-02-2017 08:48 AM
I am working with the following data:
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.
10-02-2017 09:52 AM
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;
10-02-2017 09:56 AM
Separating the data sets is easy:
data two many;
if countw(line1_regimen) > 2 then output many;
else output two;
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?
12-04-2017 10:53 AM
12-05-2017 04:03 AM
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.)