Convert excel formula to SAS

[chennupriya]

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

[Reeza]

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.

[chennupriya]

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 ?

[Reeza]

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

[Reeza]

Use the Z3. format on the field.

format variable z3.;

[chennupriya]

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

[FreelanceReinh]

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;

[Reeza]

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.  

[chennupriya]

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

[FreelanceReinh]

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

[Reeza]

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. 

[ballardw]

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.

[FreelanceReinh]

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