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
Can you show what are the 2 output data sets you need based on the data you have shown here?
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;
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?
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.)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: