Calcite | Level 5

## If statement problem

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
Super User

## Re: If statement problem

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'
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'

Thanks,

Arun

Calcite | Level 5

## Re: If statement problem

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

Super User

## Re: If statement problem

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

Calcite | Level 5

## Re: If statement problem

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.

Super User

## Re: If statement problem

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.

Calcite | Level 5

## Re: If statement problem

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.
Calcite | Level 5

## Re: If statement problem

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.
SAS Employee

## Re: If statement problem

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.

Discussion stats
• 8 replies
• 1342 views
• 0 likes
• 3 in conversation