BookmarkSubscribeRSS Feed
arunsunny
Calcite | Level 5

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

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

 

 


 

arunsunny
Calcite | Level 5

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

Reeza
Super User

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


 

arunsunny
Calcite | Level 5

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.

 

Reeza
Super User

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.

 


 

arunsunny
Calcite | Level 5
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.
arunsunny
Calcite | Level 5
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.
antonbcristina
SAS Employee

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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