topic Re: Looping and defining macro variable in SAS Programming

Looping and defining macro variable

buechler66 — Mon, 04 Apr 2016 16:35:19 GMT

I have dataset Rules with the variable Rule. I need to loop thru each record and make Rule a macro variable to be substituted for a variable in my next datastep. The number of records in Rules may vary day-to-day. Is there a best way to do this? It's some type of nested loop I think.

* For each record in Rules create a macro variable... ;

data Rules;
set Rules;
CALL SYMPUT(rule, rule);
run;

* Then how do I reference it for each record in my next dataset??? ;

data MyRuleQuery;
set MyRuleQuery;
if MyRuleQuery_Name = &rule;

run;

Re: Looping and defining macro variable

Reeza — Mon, 04 Apr 2016 16:13:40 GMT

Proc SQL is probably your best bet.

See the documentation.

A useful feature in newer versions of SAS, means that you don't need to specify the number of macro variables, it creates the amount required.

proc sql noprint;

select name into :name1-
from sashelp.class;
quit;

%let num_obs = &sqlobs;

%put &name1.;
%put &name19.;

%put &num_obs;

Re: Looping and defining macro variable

buechler66 — Mon, 04 Apr 2016 16:16:00 GMT

sorry, I had just updated my original post to make it more clear. What are your thoughts on then referencing the macro variable for each record in the next dataset?

Re: Looping and defining macro variable

Reeza — Mon, 04 Apr 2016 16:19:43 GMT

Look at the documentation link. I would create a single macro variable that held all the rules, if it fit. You have a limit of 32k or 65k characters.

Otherwise you may want to create a macro to display the rules rather than macro variables.

Or you have to nest it in a macro and loop over the values, partly why I created a macrovariable to hold the number of rules.

%do i=1 %to &num_obs;
      &&&rule&i.
%end;

Re: Looping and defining macro variable

Haikuo — Mon, 04 Apr 2016 17:16:02 GMT

Still processing your question, but it seems to me your final outcome will only need an inner SQL join? From the information I have registered, I fail to see the necessity of using macro variable.

Re: Looping and defining macro variable

ballardw — Mon, 04 Apr 2016 17:20:22 GMT

@buechler66 wrote:

sorry, I had just updated my original post to make it more clear. What are your thoughts on then referencing the macro variable for each record in the next dataset?

What are you actually going to do? You example data step code will result in an empty dataset after two interations.

If you provide some details on what you are attempting it may be that what you are looking at may be accomplished using Call Execute as it is very good way to use a control data set containg variable values that need to be used in other code.

Re: Looping and defining macro variable

buechler66 — Mon, 04 Apr 2016 18:12:20 GMT

Ok, let me try to be more specific. I have two Proc SQL steps. In the first, the QueryRules dataset has records with differenct 'Where' conditions (or snippets of SQL code) stored in the Rule variable. For example:

NVL(A.PREP_TYPE_CODE,-1) <> NVL(B.PREP_TYPE_CODE,-1)

So I'm wanting to define a macro variable for Rule in the first Proc SQL and then substitute that macro value into the Where condition of my second Proc SQL

And I need to run the second Proc SQL for each record in the first Proc SQL substituting in the Rule value. Ulitimately I'd like to append all the second Proc SQL query results into one large dataset.

I hope this makes better sense.

proc sql;
connect to oracle as db (user="me" password="mepw" path="medb");
create table QueryRules as

select * from connection to db
( select analysis_desc, rule, rule_order
into :anal_desc, :rule, :rule_order
from ivprl.analysis_ctrl
where trunc(upload_create_dt) = trunc(sysdate)
order by rule_order desc
);

disconnect from db;
quit;

proc sql;
connect to oracle as db (user="me" password="mepw" path="medb");
create table MyRuleQuery as
select * from connection to db
( select &anal_desc as RULE_NM,
b.actual_dlvry_date as AD_DT,
b.imb_code,
&rule_order as rule_order
from ivprl.bi_spm_piece_iv_recon a, ivprl.bi_spm_piece_bids_recon b
where trunc(a.upload_create_dt) = trunc(sysdate)
and trunc(b.upload_create_dt) = trunc(sysdate)
and a.imb_code = b.imb_code
and a.imb_dlvry_zip_3 in(select imb_dlvry_zip_3 from ivprl.bi_spm_piece_bids_recon)
and a.ml_cl_code in(select ml_cl_code from ivprl.bi_spm_piece_bids_recon)
and a.ml_cat_code in(select ml_cat_code from ivprl.bi_spm_piece_bids_recon)
and a.imb_code not in(select imb_code from MyRuleQueryData where rule_order < 999.1)
and &rule
);
disconnect from db;
quit;

Re: Looping and defining macro variable

buechler66 — Mon, 04 Apr 2016 19:32:55 GMT

Ok, I figured it out.

proc sql;
connect to oracle as db (user="ssbuechl" password="sha215rpe" path="ivasprd");
create table QueryRules as

select * from connection to db
( select analysis_desc, rule, rule_order
from ivprl.analysis_ctrl
where trunc(upload_create_dt) = trunc(sysdate)
and rule_order in(337,6)
order by rule_order desc
);

disconnect from db;
quit;

%macro BuildQueryData(analysis_desc= , rule= , rule_order= );
proc sql;
connect to oracle as db (user="ssbuechl" password="sha215rpe" path="ivasprd");
create table MyRuleQuery as
select * from connection to db
( select '&analysis_desc' as RULE_NM, b.actual_dlvry_date as AD_DT, b.imb_code, &rule_order as rule_order
from ivprl.bi_spm_piece_iv_recon a, ivprl.bi_spm_piece_bids_recon b
where trunc(a.upload_create_dt) = trunc(sysdate)
and trunc(b.upload_create_dt) = trunc(sysdate)
and a.imb_code = b.imb_code
and a.imb_dlvry_zip_3 in(select imb_dlvry_zip_3 from ivprl.bi_spm_piece_bids_recon)
and a.ml_cl_code in(select ml_cl_code from ivprl.bi_spm_piece_bids_recon)
and a.ml_cat_code in(select ml_cat_code from ivprl.bi_spm_piece_bids_recon)
and a.imb_code not in(select imb_code from MyRuleQueryData where rule_order < 999.1)
and &rule
);
disconnect from db;
quit;

proc append base=FinalData data = MyRuleQuery;
run;

%mend BuildQueryData;

data _null_;
set QueryRules;
call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');');
run;