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.
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:
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;
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 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;
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:
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;
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;
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.
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.
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.
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.