Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-05-2017 11:07 AM
Super User
Posts: 5,077

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

[ Edited ]

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;

 

 

 

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,368

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

[ Edited ]

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.

New Contributor
Posts: 3

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

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.
Trusted Advisor
Posts: 1,368

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


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;
Solution
‎07-05-2017 11:07 AM
Super User
Posts: 5,077

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

[ Edited ]

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;

 

 

 

 

Super User
Super User
Posts: 6,498

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

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;
New Contributor
Posts: 3

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

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?
Super User
Posts: 5,077

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

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.

Super User
Posts: 17,775

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

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 194 views
  • 3 likes
  • 5 in conversation