Hi all,
I am trying to find a MACRO that i can use to find the distinct number of patients having certain NDC numbers for different drugs. the thing is I want them to be stratified by drug names.
Here's my code for which i want to create a MACRO for.
%macro name(drug,ndc);
proc sql;
create table _02_&drug. as
select count(distinct enrolid) as patients
from data._01_md_cd_all
where NDCNUM in '&ndc.';
;
quit ;
%mend name ;
%name(evzio,'60842003001' ,'60842005101');
%name(narcan,'1223456','32432453556');
%name(bunavail,'343253254','324325446','4657687');
%name(zubsolv,'435476','64756346','6768455,'3254367','5765425234');
%name(buprenorphine,'34543645',4'324354');
%name(suboxone,'43245354645','345354365'4365465,'6456436,'6434'64,'645634',6643653');
I want the ndc as another macro. how to do that
Three steps ...
First, turn on the option that shows you what the generated SAS code looks like so you can identify errors:
options MPRINT;
Second, add parentheses and remove quotes inside the macro definition:
where NDCNUM in (&ndc.);
Third, get rid of the commas in your macro call:
%name(narcan,'1223456' '32432453556')
The IN operator does not require commas between lists of character strings, and they cause havoc in the macro processor.
This is not a good way to approach the problem, you will maintenance of that data in that way very hard and prone to mistakes. Create a dataset which contains name and the codes associated with that drug, something like:
DRUG CODE
evzio 60842003001
evzio 60842005101
narcan 1223456
...
Then use that dataset to merge onto your raw data base on ndcnum=code to get drug. This way your data can expand, shrink and change as with any other data, and its clear and easy, no macro code need, just one bit of simple merging:
proc sql;
create table _02 as
select b.drug,
count(distinct a.enrolid) as patients
from data._01_md_cd_all a
left join drugs b
on a.ndcnum=b.code
group by drug;
quit;
Or something like that. note it is generally better to do by group processing rather than create lots of similar datasets and then try to join them together, its also faster, and simpler coding.
@manya92 wrote:
Hi all,
I am trying to find a MACRO that i can use to find the distinct number of patients having certain NDC numbers for different drugs. the thing is I want them to be stratified by drug names.
Here's my code for which i want to create a MACRO for.
%macro name(drug,ndc); proc sql; create table _02_&drug. as select count(distinct enrolid) as patients from data._01_md_cd_all where NDCNUM in '&ndc.'; ; quit ; %mend name ; %name(evzio,'60842003001' ,'60842005101'); %name(narcan,'1223456','32432453556'); %name(bunavail,'343253254','324325446','4657687'); %name(zubsolv,'435476','64756346','6768455,'3254367','5765425234'); %name(buprenorphine,'34543645',4'324354'); %name(suboxone,'43245354645','345354365'4365465,'6456436,'6434'64,'645634',6643653');
I want the ndc as another macro. how to do that
Also, did you notice that your editor highlighting shows that your macro call
%name(zubsolv,'435476','64756346','6768455,'3254367','5765425234');
has errors if the commas were correct as '6768455 doesn't have a quote at the end.
Plus others such as 4'324354' '6434'64 have quotes in the middle of the value.
I think I would take a slightly different approach and create a custom format for the DSN values similar to
(completely untested as I have no data)
proc format library=work value $dsn2drug '60842003001' ,'60842005101' = 'evzio' '1223456','32432453556' ='narcan' '343253254','324325446','4657687' = 'bunavail' '435476','64756346','6768455','3254367','5765425234' = 'zubsolv' '34543645','4324354' = 'buprenorphine' '43245354645','345354365','4365465','6456436','643464','645634','6643653' = 'suboxone'
other='UNKNOWN' ; run; %macro name(drug); proc sql; create table _02_&drug. as select count(distinct enrolid) as patients from data._01_md_cd_all where upcase(put(NDCNUM,$dsn2drug.)) = upcase("&drug."); ; quit ; %mend; %name (evzio) ;
This approach would have the advantage of only adding an element to the format definition when a new code is needed.
That way you do not need to find in any of your code where the explicit value was listed and add that to the code.
You need not even place the new code on an existing line. If I were to get a new dsn for evzio I could just ad
'43241234'='evzio' at the end of the list in the format definition. Or if someone adds NDC's that have the dashes in the code values that I have seen in some data.
The UPCASE in the sql is ensure matches encase you accidentally use different capitalization in the macro call(s) at different times:
%name(Evzio)
%name (evziO) would both have the same result.
Note that with this approach you can also check your data set to see if any new codes have appeared with something like
Proc sql; title "New NDC's"; select NDCNUM from data._01_md_cd_all where put(NDCNUM,$dsn2drug.) = "UNKNOWN"; quit;
Of course if your NDC values are actual NUMERIC then no quotes should be used at all with the codes and the format name should not have the $.
Is there a particular reason for a macro?
This could be done without a macro and in one step which may be significantly more efficient.
1. Create a format for drug coding
2. Apply format within SQL and use GROUP BY to get counts into single table.
proc format;
value $ drug_class_fmt
'60842003001','60842005101' = 'evzio'
'1223456','32432453556' = 'narcan'
'343253254','324325446','4657687'= 'bunavail'
etc...
;
run;
proc sql;
create table _02_summary_counts as
select put(ndcnum, $drug_class_fmt.) as drug,
count(distinct enrolid) as patients
from data._01_md_cd_all
group by calculated drug
;
quit ;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.