Hi. I'm using dynamic sql to create a sas program with a CASE statuement containing many, many CASE WHEN statements. I use an array to produce each WHEN statement. The program flow is controlled by use of 'if _N_' and 'if EOF' statements to build the output sas file.  The 'if _N_' creates the top half of the program and the 'if EOF' creates the lower portion.     As it's written now the code works perfectly. Below is the code and the output (I've simplified the output to include only 2 CASE WHEN statements):    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; if _n_=1 then put 'proc sql;'; if _n_=1 then put 'create table QueryData&ZIP5 as '; if _n_=1 then put 'select DISTINCT '; if _n_=1 then put " a.actual_dlvry_date, "; if _n_=1 then put " a.imb_code length = 31, "; if _n_=1 then put " a.imb_dlvry_zip_5, "; if _n_=1 then put " CASE"; 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 put " ELSE ' '"; if eof then put " END as RULE_NM,"; if eof then put %nrstr("from QueryDataBase&ZIP5 as a "); if eof then put %nrstr("inner join QueryDataBase&ZIP5 as b "); if eof then put "on a.imb_code=b.imb_code "; if eof then put "where a.source='A' and b.source='B'; "; if eof then put "quit; "; run; proc sql; create table QueryData&ZIP5 as select DISTINCT a.actual_dlvry_date as ad_dt, 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 asRULE_NM length = 58 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;     However, now I need to include an additional CASE statement containing it's own WHEN statements. Since I only have program control using the 'if _N_' and 'if EOF' statements, the two CASE statements in the output sas file get all mixed together.  My desired output would look like this:   proc sql; create table QueryData&ZIP5 as select DISTINCT a.actual_dlvry_date as ad_dt, 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 length = 58, CASE WHEN ( A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN 1.0 WHEN ( A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN 1.5 ELSE . END as Rule_Order length = 5 format=4.1 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; Is there some way form me to introduce the second CASE statement between the 'if _N_' and 'if EOF' program flow statements? Any suggestions would be greatly appreciated.   As a work-around I currently create 2 output sas files - one for the top half of the program and one for the botton half of the program. Then I 'X' out of the program and copy the two files together at an OS command line.  This is inefficient as it requires two separate passes over a very, very large dataset. I'd like to build the entire output sas file it in a single pass if possible. ... View more

  I'm having trouble with my Where condition:   and a.Rule_Order is not null;   Whenever I add this condition to my query no rows are returned. I've verified that there are Rule_Order values of 1.0, 1.5, and . in the SAS dataset when I exclude this condition. So this leaves me to believe it has something to do with my Case When statement processing.   Can anyone see what it is I'm doing wrong?   proc sql; create table QueryData00926 as select DISTINCT a.actual_dlvry_date as ad_dt, 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 1.0 WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN 1.5 ELSE . END as Rule_Order length = 5 format=4.1 from QueryDataBase00926 as a inner join QueryDataBase&ZIP5 as b on a.imb_code=b.imb_code where a.source='A' and b.source='B' and a.Rule_Order is not null; quit;     ... View more