BookmarkSubscribeRSS Feed
manya92
Fluorite | Level 6

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

 

4 REPLIES 4
Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

ballardw
Super User

@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 $.

 

Reeza
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1210 views
  • 0 likes
  • 5 in conversation