topic find codelist in SAS Programming

find codelist

manya92 — Fri, 14 Sep 2018 14:00:42 GMT

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

Re: find codelist

Astounding — Fri, 14 Sep 2018 14:07:38 GMT

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.

Re: find codelist

RW9 — Fri, 14 Sep 2018 14:26:49 GMT

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.

Re: find codelist

ballardw — Fri, 14 Sep 2018 14:39:53 GMT

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

Re: find codelist

Reeza — Fri, 14 Sep 2018 14:40:15 GMT

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 ;