turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Loop in Case Statement

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-10-2013 10:43 AM

Hello,

I have requirement to convert If statements to CASE in proc sql.

My data looks as below

var1 | var2 | var3 | sum_var | condition |

a | b | c | 10 | If sum_var <5 |

a | b | c | 20 | If sum_var > 15 |

a | b | c | 30 | If sum_var > 35 |

d | e | f | 40 | If sum_var > 50 |

d | e | g | 50 | If sum_var <80 |

Now I need to create output table which satisfies group and cases in proc sql as this code will run in DI studio.

So my var1, var2 and var3 are combinations of variables. This cant be hardcoded. So I would need output something as below

var1 | var2 | var3 | sum_var | condition |

a | b | c | 10 | If sum_var <5 |

a | b | c | 20 | If sum_var > 15 |

d | e | g | 50 | If sum_var <8 |

Need your advice.

P.S.Sent from blackberry. Please ignore Spelling mistakes and typos.

Accepted Solutions

Solution

07-10-2013
01:17 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to forumsguy

07-10-2013 01:17 PM

Assuming condition is always a simple inequality comparison, you could use something like this:

**data have;****length var1 var2 var3 $4 condition $20;****input var1 var2 var3 sum_var condition &;****datalines;****a b c 10 If sum_var <5 ****a b c 20 If sum_var > 15 ****a b c 30 If sum_var > 35 ****d e f 40 If sum_var > 50 ****d e g 50 If sum_var <80 ****;**

** **

**proc sql;****create table want(drop= target keep) as****select *, **** input(scan(condition,3,' <>='), best.) as target,**** case **** when index(condition,'<=') > 0**** then sum_var <= calculated target**** when index(condition,'>=') > 0**** then sum_var >= calculated target**** when index(condition,'<') > 0**** then sum_var < calculated target**** when index(condition,'>') > 0**** then sum_var > calculated target**** else 0**** end**** as keep****from have****where calculated keep;****quit;**

PG

PG

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to forumsguy

07-10-2013 10:52 AM

You need to clarify your output and the rules you're trying to follow.

The condition is a character variable in the data set?

I don't understand the SQL restriction though, even though its DI studio, if its a code node or User written transformation you can still use data step code can't you?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

07-10-2013 11:50 AM

Completely agree with you Reeza. But since this piece of code is going to be used in extract transformation, my client is adamant on sql code.

Also, you got req correctly. I have combination of these 3 vars. The conditions are present in another variable so we need to output only observations which satisfies conditions present there.

Hope I am making sense

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to forumsguy

07-10-2013 12:12 PM

Your output doesn't seem to match your data requirements.

Can you create a format that would do it, or you have to do straight SQL. I think formats would be better...or macro variable generation for a datastep if code.

You need to post all possible conditions though, otherwise this will be a long back and forth.

Solution

07-10-2013
01:17 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to forumsguy

07-10-2013 01:17 PM

Assuming condition is always a simple inequality comparison, you could use something like this:

**data have;****length var1 var2 var3 $4 condition $20;****input var1 var2 var3 sum_var condition &;****datalines;****a b c 10 If sum_var <5 ****a b c 20 If sum_var > 15 ****a b c 30 If sum_var > 35 ****d e f 40 If sum_var > 50 ****d e g 50 If sum_var <80 ****;**

** **

**proc sql;****create table want(drop= target keep) as****select *, **** input(scan(condition,3,' <>='), best.) as target,**** case **** when index(condition,'<=') > 0**** then sum_var <= calculated target**** when index(condition,'>=') > 0**** then sum_var >= calculated target**** when index(condition,'<') > 0**** then sum_var < calculated target**** when index(condition,'>') > 0**** then sum_var > calculated target**** else 0**** end**** as keep****from have****where calculated keep;****quit;**

PG

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

07-10-2013 01:38 PM

Worked like charm. Although I have several other conditions not only inequality but your logic has helped a lot.. thanks

var1 | var2 | var3 | sum_var | condition |

a | b | c | 20 | If sum_var > 15 |

d | e | g | 50 | If sum_var <80 |