09-14-2016 02:09 PM
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.
09-14-2016 04:01 PM
@Reeza Is that really so?
What if I had this:
data person; input name $ dept $; datalines; John Sales Mary Acctng
And then I did a set of CASE/THEN statements (within a PROC SQL somewhere):
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.
09-14-2016 04:54 PM
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
09-14-2016 05:39 PM
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!
Need further help from the community? Please ask a new question.