Case Statement Help

[buechler66]

Hi. In the Proc SQL below I  use 'Case' with 'When' statements. Right now I have to create one 'When' statement for every rule stored in the &Rule_List macro variable. So if there are 100 rules then I'd need to write 100 'When' statements incrementing the second parameter to the %scan function.

I'm wondering if there might be a way to cycle thru a single 'When' statement for each rule in the &Rule_List variable without having to create 100 unique 'When' statements and without having to run the entire query that number of times.  Somehow incrementing the second parameter to the %scan function from 1 to the total number of rules in the list.

A rule list might look something like this with individual rules separated by '#'. In this case there are just two rules, but on some days there may be 100.

A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL#A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE

Does this make sense?  Would it be possible?  I'd really appreciate any help.

                proc sql;
                select trim(rule) into : rule_list separated by '#' from QueryRules;
                quit;


		proc sql;
		create table QueryData&ZIP5._&RULE_ORDER_CHAR 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 
						WHEN (%scan(&rule_list, 1,#)) AND a.RULEFLAG = "-" THEN "&RULE_ORDER"
						WHEN (%scan(&rule_list, 2,#)) AND a.RULEFLAG = "-" THEN "&RULE_ORDER"
                                                ...More
					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;

[ballardw]

I'm going to ask a stupid question: Do ALL of the rule cases have the same

THEN "&RULE_ORDER"

part of the WHEN? If so why have multiple cases?

I suspect you might be better off instead of building that obnoxious # delimited macro variable to use the individual values from your QueryRules data set.

Of course I have no idea where your other macro variables, especially &Rule_Order, is coming from but those values could well be part of QueryRules. You could have the values of another variable and using First.variable code write separate Proc SQL calls as needed if there are multiple similar actions needed, possibly that variable would be one that has the names of the variables you are referencing as

ANALYSIS_DESC.

One advantage of the call execute approach would be getting rid of multiple macro quoting/unqouting fragile code.

[buechler66]

Yes, the other macro variables come from the QueryRules ds, and their
values change according to the corresponding record.

So, you are saying there is no way to achieve the functionality I was
hoping for?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Sent from mobile

[Tom]

Make a small example of your source data for the RULES. And the corresponding code that you want to generate.

I find it is normally easier to generate the code using a DATA step than converting the data to macro variables and then trying to use macro logic to generate the code.

data rules ;
   infile cards dsd dlm='|' truncover ;
   length varname $32 rule $400 value $30 ;
   input varname -- value;
cards;
flag1|age<10|Under Age
flag1|age>20|Over Age
;;;;

data mydata ;
   input id age ;
cards;
1 5
2 12
3 25
;

filename code temp ;

data _null_;
   set rules end=eof;
   by varname ;
   file code ;
   if _n_=1 then put 'create table myresult as select id';
   if first.varname then put ',case';
   put '  when (' rule ') then ' value :$quote. ;
   if last.varname then put 'else " " end as ' varname ;
   if eof then put 'from mydata ;' ;
run;

proc sql ;
  %include code / source2 ;
quit;

[buechler66]

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;

[buechler66]

Got it!  Thanks again for the help!  Any comments or concerns at this point?

* Dynamically generate the Proc SQL with dynamically built Case When conditions for each rule. Using these Case When condtions we can identify ;
* 100+ rules in a single pass of the data. The alternative would be to run the full Proc SQL 100+ times which would be very inefficient.       ;
data _null_;
   set QueryRules end=eof;
   by Rule_Order;
   file '/home/ssbuechl/input_whens.sas';
   * Create dynamic CASE When condition to look like this: ;
   * WHEN (%scan(&rule_list,1,#)) AND a.RULEFLAG = '-' THEN '&RULE_ORDER' ;
   * And replace the 1 parameter with the current _n_ record number ;
   cur_CaseWhen_statement= tranwrd(%nrstr("WHEN (%scan(&rule_list, 1,#)) AND a.RULEFLAG = '-' THEN '&RULE_ORDER'"),"1",strip(_n_));
   if _n_=1 then put %nrstr("create table QueryData&ZIP5 as ");
   if _n_=1 then put %nrstr("select DISTINCT %unquote(%str(%')&ANALYSIS_DESC.%str(%')) as rule_nm length = 58, ");
   if _n_=1 then put "     a.actual_dlvry_date as ad_dt, ";
   if _n_=1 then put "     a.imb_code length = 31, ";
   if _n_=1 then put %nrstr("     &RULE_ORDER as rule_order, ");
   if _n_=1 then put "     a.imb_dlvry_zip_5, ";
   if _n_=1 then put "     CASE";
   put "          " cur_CaseWhen_statement;
   if eof then put %nrstr("     ELSE ' ' ";
   if eof then put "     END as RuleFlag ";
   if eof then put "from QueryData&ZIP5 as a ";
   if eof then put "inner join QueryData&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;

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
          WHEN (%scan(&rule_list, 1,#)) AND a.RULEFLAG = '-' THEN '&RULE_ORDER'
          WHEN (%scan(&rule_list, 2,#)) 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;

[Tom]

I think you are using macro quoting too much.

If when you run the program you want the macro variables VALUE to be written to the code file then use double quotes on the outside of the quoted string. So if ZIP5 = 10028 then this line:

put "create table QueryData&ZIP5 as ";

Would write:

create table QueryData10028 as 

If instead you want to write the macro variable reference to the file then use single quotes. So this line 

put 'create table QueryData&ZIP5 as ';

Would write

create table QueryData&ZIP5 as

And the macro variable ZIP5 would be resolved when you actually ran the generated code.

[RW9]

I completely agree with @Tom here.   Code generation would be easier.  I would also point out two things which seem to be consistently ignored.  SAS consists of two things - Base SAS which is the programming language, and macro which is an additional tool used to generate text which then feeds into the Base SAS compiler.  In almost no scenario is it necessary to use macro language.  Both SAS and SQL are built differntly and can both be used to attain certain functionailty.  In 99% of these cases we see examples of macro code trying to force programming in.  Deatil your actual problem, input test data (form of a datastep) and what the output should look like.

As @Tom has said, building some logic from your given clauses is very easy, however there is other things to consider such as are those logic validated, will they work, etc.