BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
Reeza
Super User

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.

JediApprentice
Pyrite | Level 9

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

Reeza
Super User

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 
JediApprentice
Pyrite | Level 9

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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