BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Abhi_Garg
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Shmuel
Garnet | Level 18

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.

Abhi_Garg
Obsidian | Level 7
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.
Shmuel
Garnet | Level 18

@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;
Astounding
PROC Star

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;

 

 

 

 

Tom
Super User Tom
Super User

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;
Abhi_Garg
Obsidian | Level 7
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?
Astounding
PROC Star

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.

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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