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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
buechler66
Barite | Level 11

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

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;

 

buechler66
Barite | Level 11

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?

Reeza
Super User

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;

 

ballardw
Super User

@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.

buechler66
Barite | Level 11

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;

Haikuo
Onyx | Level 15

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.  

buechler66
Barite | Level 11

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1161 views
  • 1 like
  • 4 in conversation