Wow! Thank you so much for this code example. With only slight alterations I was able to get exactly what I was after. And I learned a lot too! Thanks so much for taking the time to help. I really do appreciate it. Slightly modified code: data QueryRules;
Rule_Order=1;
rule_nm='ACTUAL DELIVERY DATE MISSING IN IV';
rule='A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL';
output;
Rule_Order=1.5;
rule_nm='ACTUAL DELIVERY DATE LATER IN IV';
rule='A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE';
output;
run;
filename header temp;
filename footer temp;
filename case1 temp;
filename case2 temp;
data _null_;
set QueryRules end=eof;
by Rule_Order;
/* file '/home/ssbuechl/input_whens.sas';*/
array rule_nm_array {1} $ 58 rule_nm;
array rule_array {1} $ 65 rule;
array rule_order_array {1} rule_order;
/*** Header ***/
if _n_=1 then
do;
file header;
put 'proc sql;';
put 'create table QueryData&ZIP5 as ';
put 'select DISTINCT ';
put " a.actual_dlvry_date, ";
put " a.imb_code length = 31, ";
put " a.imb_dlvry_zip_5, ";
end;
/*** Cases ***/
/* Case 1 */
file case1 mod;
if _n_=1 then
do;
put " CASE";
end;
do h = 1 to dim(rule_nm_array);
do j = 1 to dim(rule_array);
rule_nm=rule_nm_array{h};
rule=rule_array{j};
PUT ' WHEN (' RULE ') THEN trim("' RULE_NM '")';
end;
end;
if eof then
do;
put " ELSE ' '";
put " END as RULE_NM,";
end;
/* Case 2 */
file case2 mod;
if _n_=1 then
do;
put " CASE";
end;
do x = 1 to dim(rule_order_array);
do y = 1 to dim(rule_array);
rule_nm=rule_nm_array{x};
rule=rule_array{y};
PUT ' WHEN (' RULE ') THEN trim("' RULE_ORDER '")';
end;
end;
if eof then
do;
put " ELSE ' '";
put " END as RULE_ORDER";
end;
/*** Footer ***/
if eof then
do;
file footer;
put %nrstr("from QueryDataBase&ZIP5 as a ");
put %nrstr("inner join QueryDataBase&ZIP5 as b ");
put "on a.imb_code=b.imb_code ";
put "where a.source='A' and b.source='B'; ";
put "quit; ";
end;
run;
filename all temp;
/*filename all '/home/ssbuechl/input_whens.sas';*/
data _null_;
/* file all;*/
FILE 'C:\TESTME.SAS';
/* header */
last=0;
do until(last);
infile header end=last;
input;
put _infile_;
end;
/* case 1 */
last=0;
do until(last);
infile case1 end=last;
input;
put _infile_;
end;
/* case 2 */
last=0;
do until(last);
infile case2 end=last;
input;
put _infile_;
end;
/* footer */
last=0;
do until(last);
infile footer end=last;
input;
put _infile_;
end;
stop;
run;
/*data _null_;*/
/* file print;*/
/* infile all;*/
/* input;*/
/* put _infile_;*/
/*run;*/
filename header clear;
filename footer clear;
filename case1 clear;
filename case2 clear; Output TestMe.sas: proc sql;
create table QueryData&ZIP5 as
select DISTINCT
a.actual_dlvry_date,
a.imb_code length = 31,
a.imb_dlvry_zip_5,
CASE
WHEN (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN trim("ACTUAL DELIVERY DATE MISSING IN IV ")
WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN trim("ACTUAL DELIVERY DATE LATER IN IV ")
ELSE ' '
END as RULE_NM,
CASE
WHEN (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN trim("1 ")
WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN trim("1.5 ")
ELSE ' '
END as RULE_ORDER
from QueryDataBase&ZIP5 as a
inner join QueryDataBase&ZIP5 as b
on a.imb_code=b.imb_code
where a.source='A' and b.source='B';
quit;
... View more
