Hi, I am working on some drug data set and need to specify patients using some drugs. The problem is we have some drug name but the data set contains not exactly same but similar name. The drug name in dataset may contain more word/letter than the name we have in hand. I just used exact match to identify patients using these drugs, so the result looks not correct. My code looks like the following. I just listed two names here, but actually I have many drug names.
%macro drugs;
%do i = 1 %to 10;
data drug;
set drug;
drug_1=0;
drug_2=0;
if brandname in ('ADOXA', 'VECTRIN')
or genname in ('DOXYCYCLINE HYCLATE', 'QUINACRINE')
then drug_1=1;
if brandname in ('CIMZIA', 'SIMPONI ARIA')
or genname in ('ANAKINRA', 'GOLIMUMAB')
then drug_2=1;
RUN;
%end;
%mend;
%drugs;
Too few patients were identified by the above code. I am thinking to use proc sql and like 'pattern' or =* operator, but not sure if it is a good way to do and also not sure if like or =* can be used with more than one drug names to do search. I am also concerning if sas can do macro and proc sql together. Could you help me write some sample code? Thanks so much!
Could you provide some examples of names which are not identical but which you want to be recognised as a match, please?
Do you have any issues with the case when matching? Currently you are searching for 'ADOXA'. Do you have values like 'Adoxa' in you your data? If so first step I would try would be:
If strip(upcase(brandname)) in ('ADOXA', 'VECTRIN')
or strip(upcase(genname)) in ('DOXYCYCLINE HYCLATE', 'QUINACRINE')
then drug_1=1;
The strip is included in case any of your entered values have leading or trailing blanks. ' ADOXA' would not match 'ADOXA'.
Extra words or letters may mean a more complicated search and you'll likely lose the IN operator and have to test for each of your values separately.
If index(upcase(brandname),'ADOXA') > 0 then drug1_1=1; /* then ADOXA is in the string somewhere*/
or
Find(brandname, 'ADOXA','it') > 0 then drug1_1=1;
I strongly recommend getting something to work before looking at macros. Your existing shell macro processes the same data set 10 time exactly the same way. And using the:
data data;
set data;
construct in a macro is just asking for a case where you have an error and can't determine where it happened.
Thanks so much ballardw! It works!
Either like or =* can't be used to multiple matched. Why not try Hash Table or PRX .
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 25. 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.