topic Re: Executing Macro from Data step iteratively and combing each result in one dataset in SAS Procedures

Executing Macro from Data step iteratively and combing each result in one dataset

Abhi_Garg — Mon, 03 Jul 2017 19:13:19 GMT

Hi folks,

Problem Definition: In my code, I have a Macro (claims_extraction) which connects to Database (Netezza) and extracts data.

Then I have a list of Drugs, Which I defined as a Macro variable (drug_list). The macro should run for each drug in the list and return one dataset as the output. I have been struggling to combine the dataset created by macro execution.

Code:

Here is how my macro looks like:

%macro claims_extraction(dataset,drug_name);
Proc sql;
&DWQUERYCONNECT.;
create table &dataset. as
select * &DWFROMCONNECT. 
(
select QNXT_CLAIM_ID, .......<other variables and table joins>
where upper(drugs.DRUG_NAME) like %UPCASE(&drug_name.)
order by mhn, svc_date;
);
quit;
%mend;

Below is the drug_list macro variable:

%let drug_list = '%Advair%' '%Dulera%' '%Symbicort%' '%Breo%Ellipta%';

I am using call execute routine to iteratively execute the macro:

data test;

data _null_;
	do i = 1 to countw("&drug_list",' ');
	 call execute('%claims_extraction(abc,'||scan("&drug_list",i,' ')||');');
	end;
run;

It works all fine without any error. But it creates one dataset for each drug and since I have 'abc' name final argument, each drug overwrites the previous result and end up only with the records for the last drug from the list.

Question: How can I modify my data step to give me one dataset as result for all the drugs on the list?

Abhi.

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Shmuel — Mon, 03 Jul 2017 19:27:19 GMT

Try change your last part as to:

data _null_;
	do i = 1 to countw("&drug_list",' ');
             drug_name = scan("&drug_list",i,' ');
	     call execute('%claims_extraction(abc'||compress(drug_name,"%")||','||drug_name||');');
	end;
run;

then each dataset will be 'abc'||drug_name.

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Astounding — Mon, 03 Jul 2017 19:54:20 GMT

Surely you would be better of running the macro once, hitting the database once, and avoiding the complications of CALL EXECUTE. Why not change the original query:

Expand the WHERE conditions so they get all the drugs you want,
Change the ORDER BY so that the drug name is the first item in the list

For example just call the macro once without CALL EXECUTE, but change the ending:

%let n_drugs = %sysfunc(countw(&drug_list));

where

%do i = 1 %to &n_drugs;

%let next_drug = %scan(&DRUG_LIST, &i, %str( ));

upper (drugs.DRUG_NAME) like %UPCASE(&next_drug.)

%if &i < &n_drugs %then or;

%end;

order by drug_name, mhn, svc_date;

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Abhi_Garg — Mon, 03 Jul 2017 19:36:16 GMT

Nope it won't work. Compress removes the % from the drugs but then the macro variable for data set name resolves to abc'Advair', which is illegal SAS dataset name and hence entire code fails.

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Tom — Mon, 03 Jul 2017 19:45:23 GMT

You should probably change your process to just query the database once.

But to your actual question just add PROC APPEND steps to aggregate the results. You might want to make sure the target aggregate file does not exist before starting.

proc delete data=want ; run;
data _null_;
  do i = 1 to countw("&drug_list",' ');
    call execute('%claims_extraction(abc,'||scan("&drug_list",i,' ')||');');
    call execute('proc append base=want data=abc force; run;');
  end;
run;

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Shmuel — Mon, 03 Jul 2017 19:53:03 GMT

@Abhi_Garg wrote:
Nope it won't work. Compress removes the % from the drugs but then the macro variable for data set name resolves to abc'Advair', which is illegal SAS dataset name and hence entire code fails.

then compress the apostoph too:

data _null_;
	do i = 1 to countw("&drug_list",' ');
             drug_name = scan("&drug_list",i,' ');
	     call execute('%claims_extraction(abc'||compress(drug_name,"%'")||','||drug_name||');');
	end;
run;

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Reeza — Mon, 03 Jul 2017 20:28:09 GMT

1. Don't do it this at, do it all at once as others have indicated.

2. Add a constant prefix so you can append data all at once after.

3. Add an append step into your macro to append to a master table each time.

A good paper to read is Dont be Loopy by David Cassell. It goes over why you shouldn't loop and how to do simulations in SAS.

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Abhi_Garg — Mon, 03 Jul 2017 21:40:02 GMT

I totally agree that I shouldn't query the database so many times. But I am kinda new to SAS, I am not sure how I can dynamically change my where clause for each of the drug in list. Can you please help me with changing my where clause?

Re: Executing Macro from Data step iteratively and combing each result in one dataset

Astounding — Mon, 03 Jul 2017 21:45:29 GMT

I'm not sure if you're asking me or not ... these message boards don't really show the connections between posts. But if you are asking about my program, it does that automatically. Whatever is part of &DRUG_LIST will be added to the WHERE clause by the posted code.