Thanks so much for the sample code. It helped more than you could know. I really appreciate your time. I have it creating the sas code now and outputing to a file, but one issue remains. The text %scan(&rule_list, 1,#) resolves in the output file as (&rule_list). I need the entire string to be output as: %scan(&rule_list, 1,#). Is there something I can do to preserve the string? I will also then need to find a way to increment the second parameter of the %scan function by one for each statement generated. The code: data QueryRules;
infile cards dsd dlm='|' truncover ;
informat analysis_ctrl $34. rule $64.;
input analysis_ctrl $ rule $ rule_order;
cards;
ACTUAL DELIVERY DATE MISSING IN IV|A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL|1
ACTUAL DELIVERY DATE LATER IN IV|A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE|1.5
run;
* Recodes to fix inconsistancies in Rule code so it will run on IBMs database and run using SAS instead of plsql ;
data QueryRules;
length rule $99. ;
set QueryRules;
* Create char variable to use in ds names ;
rule_order_char = TRANWRD(rule_order,".","_");
* Update schema references from USPS name to IBM name;
if rule_order = 400 then do;
rule = tranwrd(rule,"IV_RPT.","iv_ora.");
end;
* SASs equiv of Oracles NVL function ;
rule = tranwrd(rule,"NVL(", "COALESCE(");
* Rule 6: CRITICAL ENTRY TIME MISMATCH (handle as time, not date) ;
if rule_order = 6 then rule = tranwrd(rule,"SYSDATE",quote('X'));
* Handle as dates ;
else rule = tranwrd(rule,"SYSDATE","today()");
* Rule 332: LAST_SCAN_MACH_TYPE ;
* Search and replace -1 with X ;
if rule_order = 332 then rule = tranwrd(rule,"-1",quote('X'));
run;
* Sort prep for call to BuildQueryData macro below ;
proc sort data=QueryRules;
by rule_order;
run;
data _null_;
set QueryRules end=eof;
by Rule_Order;
file '/home/ssbuechl/input_whens.sas';
if _n_=1 then put "create table QueryData&ZIP5 as
select DISTINCT %unquote(%str(%')&ANALYSIS_DESC.%str(%')) as rule_nm length = 58,
a.actual_dlvry_date as ad_dt,
a.imb_code length = 31,
&RULE_ORDER as rule_order,
a.imb_dlvry_zip_5,
CASE";
PUT "WHEN (%scan(&rule_list, 1,#)) AND a.RULEFLAG = '-' THEN '&RULE_ORDER'";
if eof then put "ELSE ' ' END as RuleFlag
from QueryData&ZIP5 as a
inner join QueryData&ZIP5 as b
on a.imb_code=b.imb_code
where a.source='A' and b.source='B';
quit;";
run; The output file looks like this: create table QueryData&ZIP5 as
select DISTINCT '&ANALYSIS_DESC.' as rule_nm length = 58,
a.actual_dlvry_date as ad_dt,
a.imb_code length = 31,
&RULE_ORDER as rule_order,
a.imb_dlvry_zip_5,
CASE
WHEN (&rule_list) AND a.RULEFLAG = '-' THEN '&RULE_ORDER'
WHEN (&rule_list) AND a.RULEFLAG = '-' THEN '&RULE_ORDER'
ELSE ' ' END as RuleFlag
from QueryData&ZIP5 as a
inner join QueryData&ZIP5 as b
on a.imb_code=b.imb_code
where a.source='A' and b.source='B';
quit;
... View more
