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

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Convert excel formula to SAS

- 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 05-12-2016 01:36 PM
(1709 views)

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

12 REPLIES 12

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

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

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

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

Use the Z3. format on the field.

format variable z3.;

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

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

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

Hmm...as usual @FreelanceReinh 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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

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

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.