Hello,
I have a dataset with over 10,000 drugs list. I will like to categorize each drug into specific group. However, some drugs are likely to be in 2 or 3 groups e.g., Prochlorperazine could be antipsychotics or antiemetics. I want Prochlorperazine to be group as antipsychotics if found and also antiemetics if found so that Prochlorperazine will be in both groups just like the table below. I have tried to use the FIND function or the UPCASE =: to group the drugs but this methods could not assign a drug into different group if that's the case for the drug. SAS only output the drug into the last category.
Here is what I want to achieve with table of drugs and the categories.
Drug | Category |
Prochlorperazine | Antipsychotics |
Prochlorperazine | Antiemetics |
Celecoxib | Analgesics |
Celecoxib | Anti-inflammatory Agents |
Diclofenac | Anti-inflammatory Agents |
Diclofenac | Dermatological Agents |
Diclofenac | Ophthalmic Agents |
Diclofenac | Analgesics |
This is my code but did not solve this problem. Or is this logic impossible to do?
data check2;
length category $80;
set check1;
if upcase (drug) =: "Celecoxib" then do;
category = "Analgesics";
end;
else if upcase (drug) =: "Celecoxib" then do;
category = "Anti-inflammatory Agents";
end;
run;
Depending on what you expect to do later with the "multiple group" this may be done with a multilabel format. The question about what you intend to do is because only a very few procedures will use the multiple labels.
The only procedures that will use the multiple label are Procs Tabulate, Report, Means and Summary and the results can vary depending on the way you define the labels, the order, and options used when displaying them.
If you only want the drugs and categories it could be done with a data step BUT almost certainly will not behave as you expect if there are other variables involved. For example, this will output both levels. However this duplicates observations and once you have an explicit output statement you need many others for your output set. Your UPCASE value of Drug would never match your condition, you need to provide an uppercase version of the drug name. If you actually have mixed cases of your drug name in the data I would address that as a separate step.
Data check2; length category $80; set check1; if upcase (drug) =: "CELECOXIB" then do; category = "Analgesics"; output; category='Anti-inflammatory Agents'; output; end;
This is a basic demonstration of different definitions of multilabel formats for similar values and the appearance results, creating a data set to use the values and options for reporting.
/* To demonstrate how the order of definition affects appearance in multilabel formats. Also appearance options to show the spaces to get the indent as desired and fix column widths. And investigate whether class level format based style overrides work with MLF in proc tabulate. Result: NO. */ proc format library=work; value accidentl (multilabel notsorted) 1-5 = 'Accidents' 1-3 = ' Transport accidents' 1 = ' Motor vehicle accidents' 2 = ' Water, air, and space' 3 = ' Other land transport accidents' 4-5 = ' Nontransport accidents' 5 = ' Fishing' ; value accidentr (multilabel notsorted) 1-5 = 'Accidents' 1-3 = ' Transport accidents' 4-5 = ' Nontransport accidents' 1 = ' Motor vehicle accidents' 2 = ' Water, air, and space' 3 = ' Other land transport accidents' 5 = ' Fishing' ; value accidents (multilabel notsorted) 1 = ' Motor vehicle accidents' 2 = ' Water, air, and space' 3 = ' Other land transport accidents' 5 = ' Fishing' 1-5 = 'Accidents' 1-3 = ' Transport accidents' 4-5 = ' Nontransport accidents' ; value mf 1 = "Male" 2 = "Female" ; value mycolor 1='white' 2='red' 3='green' 4='blue' 5='orange' 6='purple' ; value accsimple 1 = 'Motor vehicle accidents' 2 = 'Water, air, and space' 3 = 'Other land transport accidents' 4 = 'Nontransport accidents' 5 = 'Fishing' ; value MyColorl (multilabel notsorted) 1-5 = 'white' 1-3 = 'red' 1 = 'blue' 2 = 'orange' 3 = 'pink' 4-5 = 'purple' 5 = 'black' ; run; /* populate a dataset to display */ /* This specifically does NOT generate any data for FISHING above*/ /* to display the behavior of the options below in those cases. */ data junk; do i=1 to 50; type = round(4*ranuni(1234)+.5); sex = round(2*ranuni(3455)+.5); output; end; run; /* Notice that before we get here the data is NOT sorted */ /* in any manner!!!! */ title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl'; proc tabulate data=junk order=data ; class type / mlf PRELOADFMT; /*ASIS preserves the leading spaces in the format, Cellwidth here is optional for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF not the OUTPUT window */ classlev type/ style=[asis=on cellwidth=1.5in]; class sex; /* the formatted values of SEX normally take up different lengths, this fixes the column widths to be the same for both headers. May cause interesting appearances with large numbers of displayed digits if requested in formats*/ classlev sex/ style=[cellwidth=.5in]; /* to get the ALL to have the same width as SEX need to specify this way*/ table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8. / printmiss misstext='0'; format type accidentl. sex mf.; run;title; title 'Option simple format and classlev background color'; proc tabulate data=junk order=data ; class type / ; /*ASIS preserves the leading spaces in the format, Cellwidth here is optional for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF not the OUTPUT window */ classlev type/ style=[asis=on cellwidth=1.5in background=mycolor.]; class sex; /* the formatted values of SEX normally take up different lengths, this fixes the column widths to be the same for both headers. May cause interesting appearances with large numbers of displayed digits if requested in formats*/ classlev sex/ style=[cellwidth=.5in]; /* to get the ALL to have the same width as SEX need to specify this way*/ table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8. / row=float misstext='0'; format type accsimple. sex mf.; run;title; title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl classlev background'; proc tabulate data=junk order=data; class type / mlf PRELOADFMT; classlev type/ style=[asis=on cellwidth=1.5in background=mycolor.]; class sex; classlev sex/ style=[cellwidth=.5in]; table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8. / printmiss misstext='0'; format type accidentl. sex mf.; run;title; title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidents'; proc tabulate data=junk order=data; class type / mlf PRELOADFMT; classlev type/ style=[asis=on cellwidth=1.5in]; class sex; classlev sex/ style=[cellwidth=.5in]; table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8. / printmiss misstext='0'; format type accidents. sex mf.; run;title;
If you have an external data set that has the drugs and categories that could be used to create formats but this is likely to require multiple formats depending on the specific use wanted.
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: