DATA Step, Macro, Functions and more

Proc SQL - IF/THEN Conditions

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Proc SQL - IF/THEN Conditions

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.


Accepted Solutions
Solution
‎09-15-2016 09:11 AM
Super User
Posts: 19,869

Re: Proc SQL - IF/THEN Conditions

Posted in reply to buechler66

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.

View solution in original post


All Replies
Solution
‎09-15-2016 09:11 AM
Super User
Posts: 19,869

Re: Proc SQL - IF/THEN Conditions

Posted in reply to buechler66

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.

Frequent Contributor
Posts: 123

Re: Proc SQL - IF/THEN Conditions

@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. 

Super User
Posts: 19,869

Re: Proc SQL - IF/THEN Conditions

Posted in reply to JediApprentice

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 
Frequent Contributor
Posts: 123

Re: Proc SQL - IF/THEN Conditions

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 4004 views
  • 4 likes
  • 3 in conversation