## Convert excel formula to SAS

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

## Re: Convert excel formula to SAS

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.

## Re: Convert excel formula to SAS

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 ?

## Re: Convert excel formula to SAS

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

## Re: Convert excel formula to SAS

Use the Z3. format on the field.

format variable z3.;

## Re: Convert excel formula to SAS

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

## Re: Convert excel formula to SAS

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;``````

## Re: Convert excel formula to SAS

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.

## Re: Convert excel formula to SAS

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

## Re: Convert excel formula to SAS

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

# The ZW format Writes standard numeric data with leading 0s, and standardizes it to length W, regardless of the length of the input variable.

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.

## Re: Convert excel formula to SAS

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.

## Re: Convert excel formula to SAS

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.)

Discussion stats
• 12 replies
• 1710 views
• 6 likes
• 4 in conversation