topic Re: If statement problem in SAS Programming

If statement problem

arunsunny — Fri, 09 Feb 2018 17:26:41 GMT

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

Re: If statement problem

Reeza — Fri, 09 Feb 2018 17:28:52 GMT

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

Re: If statement problem

arunsunny — Fri, 09 Feb 2018 17:37:11 GMT

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

Re: If statement problem

Reeza — Fri, 09 Feb 2018 17:39:41 GMT

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

Re: If statement problem

arunsunny — Fri, 09 Feb 2018 17:55:46 GMT

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.

Re: If statement problem

Reeza — Fri, 09 Feb 2018 18:04:30 GMT

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.

Re: If statement problem

arunsunny — Fri, 09 Feb 2018 18:10:20 GMT

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.

Re: If statement problem

arunsunny — Fri, 09 Feb 2018 18:13:54 GMT

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.

Re: If statement problem

antonbcristina — Fri, 09 Feb 2018 18:13:56 GMT

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.