topic Re: Proc SQL - IF/THEN Conditions in SAS Programming

Proc SQL - IF/THEN Conditions

buechler66 — Wed, 14 Sep 2016 18:09:48 GMT

Hi. Is there anyway to translate this type of IF/THEN data step coding into Proc SQL coding? Essentially I need to test a series of rule conditions and if a rule is true then I need to set the analysis_desc and rule_order variables to specific values. The tricky part is that once a rule condition is met for a record then no other rule conditions should be tested for that record.

     if (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN 
		do;
			analysis_desc=trim("ACTUAL DELIVERY DATE MISSING IN IV ");
			rule_order=1.0;
		end;
	 ELSE IF (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN 
		do;
			analysis_desc=trim("ACTUAL DELIVERY DATE LATER IN IV ");
			rule_order=1.0;
		end;
     ELSE do;
		analysis_desc='';
		rule_order=.;
	 END;

I had been trying to use Case statements, but found I couldn't figure out a way to stop the code after the first rule condtion was true.

    CASE
           WHEN (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN "ACTUAL DELIVERY DATE MISSING IN IV"
           WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN "ACTUAL DELIVERY DATE LATER IN IV"
     ELSE ''
     END as Analysis_Desc,
     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

I need to use Proc SQL in this case because the rule condtions are provided to us as Oracle SQL Where conditions and must be integrated into my SAS program.

I'd really appreciate any ideas for accomplishing this type of functionality in my SAS program.

Re: Proc SQL - IF/THEN Conditions

Reeza — Wed, 14 Sep 2016 18:31:02 GMT

That's how CASE/WHEN statements work. They end if one condition is met. P

Your code as written should work. Please explain how it's not with a data example.

Re: Proc SQL - IF/THEN Conditions

JediApprentice — Wed, 14 Sep 2016 20:01:47 GMT

@Reeza Is that really so?

What if I had this:

data person;
   input name $ dept $;
   datalines;
John Sales
Mary Acctng
Mark Marketing

And then I did a set of CASE/THEN statements (within a PROC SQL somewhere):

CASE

WHEN name='John' and dept='Sales' THEN 'John from Sales'

WHEN name='Mary' and dept='Acctng' THEN 'Mary from Accounting'

WHEN name='Mark' and dept='Marketing' THEN 'Mark from Marketing'

END AS People,

It doesn't exit the CASES after the first WHEN (which is a condition that is met). Explain this.

Re: Proc SQL - IF/THEN Conditions

Reeza — Wed, 14 Sep 2016 20:54:36 GMT

I think you're misunderstanding how the Case statement.

Case-When operates on each line of the data set. Once a condition is met for that line it stop and then proceeds to the next line of the dataset.

It sounds like you might want a set operation? So once some condition is met, it stops processing all further rows of data?

What would you expect to happen?

The results are following:

Obs name dept People 
1 John Sales John from Sales 
2 Mary Acctng Mary from Accounting 
3 Mark Marketing Mark from Marketing

Re: Proc SQL - IF/THEN Conditions

JediApprentice — Wed, 14 Sep 2016 21:39:56 GMT

Ahh I see. This makes a lot more sense now. I was under the impression that if one line from the data met the condition it will exit from the data, but now I see that it continues to iterate through the datalines to see if the same condition is met. Thank you!