Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- If statement problem

Options

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-09-2018 12:25 PM
(1341 views)

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

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Are you ready for the spotlight? We're accepting content ideas for **SAS Innovate 2025** to be held May 6-9 in Orlando, FL. The call is **open **until September 25. Read more here about **why** you should contribute and **what is in it** for you!

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.