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
- /
- General Programming
- /
- Re: If statement problem

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

2 weeks ago - last edited 2 weeks ago

Hi,

I want to use an IF statement to create output for column H using the logic I have written below. There would be a dynamic calculation while executing the IF statement. Please let me know if this can work dynamically & want a solution.

NEW_GRP Logic for 1 set (i.e. Cash & vol set) - Refer Excel file attached. I have multiple records in my data set and the following condition would apply for all records.

IF CAT = 'CASH' AND VOL = 'Small' AND (KPI <= MEAN) THEN NEW_GRP = 'NIL'

IF CAT = 'CASH' AND VOL = 'Medium' AND (KPI > MEAN and KPI < SD1) THEN NEW_GRP = 'Low'

IF CAT = 'CASH' AND VOL = 'Medium' AND (KPI > SD1 and KPI < SD2) THEN NEW_GRP = 'High'

IF CAT = 'CASH' AND VOL = 'Medium' AND (KPI >= SD2) THEN NEW_GRP = 'Very High'

I currently use SAS EG 7.1 64 bit (SAS 9.4 built in).

Thanks,

Arun

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

Posted in reply to arunsunny

2 weeks ago - last edited 2 weeks ago

It's not clear what's 'dynamic' about your IF statements.

Are they not working? I'm not sure what your question is here.

EDIT: you should also use IF/ELSE IF rather than multiple IF statements. Especially if it's possible to have multiple IF statements be true.

arunsunny wrote:

Hi,

I want to use an IF statement to create output for column H using the logic I have written below. There would be a dynamic calculation while executing the IF statement. Please let me know if this can work dynamically & want a solution.

NEW_GRP Logic for 1 set (i.e. Cash & vol set) - Refer Excel file attached. I have multiple records in my data set and the following condition would apply for all records.

IF CAT = 'CASH' AND VOL = 'Small' AND (KPI <= MEAN) THEN NEW_GRP = 'NIL'ELSEIF CAT = 'CASH' AND VOL = 'Medium' AND (KPI > MEAN and KPI < SD1) THEN NEW_GRP = 'Low'ELSEIF CAT = 'CASH' AND VOL = 'Medium' AND (KPI > SD1 and KPI < SD2) THEN NEW_GRP = 'High'ELSEIF CAT = 'CASH' AND VOL = 'Medium' AND (KPI >= SD2) THEN NEW_GRP = 'Very High'

Thanks,

Arun

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

Posted in reply to Reeza

2 weeks ago

To provide more clarity, i need the output in column H to be this:

=IF(C2<=D2,"Nil",IF(AND(C2>E2,C2<=F2),"Low", IF(AND(C2>E2,C2<=F2),"High","Very High")))

i can do this in excel with formula above, but i want to use the same logic in SAS using IF statement.

Thanks,

Arun

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

Posted in reply to arunsunny

2 weeks ago

Your IF statements are correct, did you try it and not have it work?

I still don't understand what you're asking for here, ie what kind of answer are you looking for?

arunsunny wrote:

To provide more clarity, i need the output in column H to be this:

=IF(C2<=D2,"Nil",IF(AND(C2>E2,C2<=F2),"Low", IF(AND(C2>E2,C2<=F2),"High","Very High")))

i can do this in excel with formula above, but i want to use the same logic in SAS using IF statement.

Thanks,

Arun

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

Posted in reply to Reeza

2 weeks ago - last edited 2 weeks ago

The columns C to G have values and when you reference this in excel, i get result. In SAS i get blank data. If I hard code the values i get a result.

Logic with values hard coded for columns C to G - Works well in SAS

IF CAT = 'CASH' AND VOL = 'Small' AND KPI <= 0.00177 THEN NEW_GRP = 'NIL'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > 0.00177 and KPI < 0.00578 THEN NEW_GRP = 'Low'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > 0.00578 and KPI < 0.01379 THEN NEW_GRP = 'High'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI >= 0.01379 THEN NEW_GRP = 'Very High'

This doesnt work in SAS:

IF CAT = 'CASH' AND VOL = 'Small' AND KPI <= MEAN THEN NEW_GRP = 'NIL'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > MEAN and KPI < SD1 THEN NEW_GRP = 'Low'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > SD1 and KPI < SD2 THEN NEW_GRP = 'High'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI >= SD2 THEN NEW_GRP = 'Very High'

My problem is it take lot of time to hard code multiple values (columns C to G) for multiple sets within the logic. Hope this is clear now.

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

Posted in reply to arunsunny

2 weeks ago

This doesnt work in SAS:

Does "this doesn't work" mean "You get blank values"?

What does the log show? Can you provide sample data as text so we can run your code?

The code is fine, if its not working you likely are referencing your variables incorrectly. The log will usually indicate this.

arunsunny wrote:

The columns C to G have values and when you reference this in excel, i get result. In SAS i get blank data. If I hard code the values i get a result.

Logic with values hard coded for columns C to G - Works well in SAS

IF CAT = 'CASH' AND VOL = 'Small' AND KPI <= 0.00177 THEN NEW_GRP = 'NIL'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > 0.00177 and KPI < 0.00578 THEN NEW_GRP = 'Low'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > 0.00578 and KPI < 0.01379 THEN NEW_GRP = 'High'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI >= 0.01379 THEN NEW_GRP = 'Very High'

This doesnt work in SAS:

IF CAT = 'CASH' AND VOL = 'Small' AND KPI <= MEAN THEN NEW_GRP = 'NIL'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > MEAN and KPI < SD1 THEN NEW_GRP = 'Low'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI > SD1 and KPI < SD2 THEN NEW_GRP = 'High'

ELSE IF CAT = 'CASH' AND VOL = 'Medium' AND KPI >= SD2 THEN NEW_GRP = 'Very High'

My problem is it take lot of time to hard code multiple values (columns C to G) for multiple sets within the logic. Hope this is clear now.

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

Posted in reply to Reeza

2 weeks ago

Does "this doesn't work" mean "You get blank values"? - YES

What does the log show? Can you provide sample data as text so we can run your code? - You can use the attached excel in my 1st post to see data & logic.

SAS Log:

NOTE: Variable NEW_GRP is uninitialized.

What does the log show? Can you provide sample data as text so we can run your code? - You can use the attached excel in my 1st post to see data & logic.

SAS Log:

NOTE: Variable NEW_GRP is uninitialized.

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

Posted in reply to arunsunny

2 weeks ago

I'm sorry it works now. The problem was in THEN statement, i had the incorrect column name. I apologize for silly mistake. Thank you Reeza.

Highlighted
## Re: If statement problem

Options

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

Posted in reply to arunsunny

2 weeks ago

Are you using the code you posted as is? You are missing semicolons at the end of every statement which is probably why SAS is complaining about uninitialized variables.