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
- /
- SAS Procedures
- /
- Convert excel formula to SAS

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-12-2016 01:36 PM

Hi ,

I have an excel sheet which i have drew below and i need to create the values for coloumn C

and the formula is

=IF($A2="Pricing", IF($B2>=0, IF(LEN($B2)=1, "+00"&$ B2, IF(LEN($B2)=2, "+0"&$ B2, IF(LEN($B2)=3, "+"&$ B2))), IF(LEN($B2)=2, "-00"&ABS($B2), IF(LEN($B2)=3, "-0"&ABS($B2), IF(LEN($B2)=4, "-"&ABS($B2))))),"")

Can anyone convert this code to SAS code so that the values in coloumn C would be

-099

-039

-009

+000

+000

+014

Items | Value | Sales |

Pricing | -99.0000 | |

Pricing | -39.0000 | |

Pricing | -9.0000 | |

Pricing | 0.0000 | |

Pricing | 0.0000 | |

Pricing | 39.0000 | |

Pricing | 14.0000 | |

Pricing | 999.0000 |

Thanks

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

Posted in reply to chennupriya

05-12-2016 01:55 PM

Can you translate the Excel to pseudo code or summarize the logic.

It's easier to transfer those rules into SAS than your formula. Also, I don't want to take the time to decode it.

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

Posted in reply to Reeza

05-12-2016 01:57 PM

Hi,

I just have the excel formula and here A means coloum name called items and B means coloumn name Value

and using the above formula i need to create Coloumn C Sales but can you replicate the same formula into SAS Code ?

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

Posted in reply to chennupriya

05-12-2016 02:04 PM

Not the exact same formula and I would hope you would understand the logic behind what you're trying to do...

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

Posted in reply to Reeza

05-12-2016 02:07 PM

Use the Z3. format on the field.

format variable z3.;

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

Posted in reply to Reeza

05-12-2016 02:00 PM - edited 05-12-2016 02:01 PM

and to get sales value -099 then we input B2 and when we want sales value -039 the formula we change B2 to B3 and A2 to A3 and so .....

Thanks

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

Posted in reply to chennupriya

05-12-2016 02:18 PM

Hi @chennupriya,

Try this:

```
data want;
set have;
length Sales $4;
if items='Pricing' then Sales=ifc(value>=0, cats('+', put(value, z3.)), put(value, z4.));
run;
```

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

Posted in reply to FreelanceReinhard

05-12-2016 02:36 PM

Hmm...as usual @FreelanceReinhard is correct

But, my suggestion would be to build your own custom format and keep it as a numeric value rather than convert it to a character variable. I would still emphasize that you should know what your formula's are doing, always.

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

Posted in reply to FreelanceReinhard

05-12-2016 04:09 PM

Hi ,

But the addition of zeros are for both positive and negative values and also they are dpended upon length of the value how does we implement in the code

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

Posted in reply to chennupriya

05-12-2016 04:14 PM

@chennupriya: Did you observe a case where the result of either solution did not match your expectations?

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

Posted in reply to chennupriya

05-12-2016 06:28 PM

Z3 - states a length of 3

Z4 - states a length of 4

If your case is different than what you've initially presented you need to explain in detail how and what you want.

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

Posted in reply to chennupriya

05-12-2016 02:49 PM - edited 05-12-2016 02:50 PM

If your values are numeric a simple custom format will do this. If your values aren't numeric then why not?

```
proc format library=work;
picture Mysign
low - < 0 = '0999' (prefix='-')
0 - high = '0999' (prefix='+')
;
run;
data junk;
do x = -99,-39,-9,0,39,14, 999;
output;
end;
run;
proc print data=junk noobs;
format x Mysign.;
run;
```

if you need to display larger values add more 9 to the '0999' in the format, the 0 is there for a placeholder to put the + or - into.

If you need decimals then '0999.99' or similar.

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

Posted in reply to ballardw

05-12-2016 03:02 PM

Indeed, a picture format is the best way to accomplish this task. (I'm always slow in creating picture formats. That's why I preferred to suggest the character function approach.)