topic Re: Convert excel formula to SAS in SAS Procedures

Convert excel formula to SAS

chennupriya — Thu, 12 May 2016 17:36:41 GMT

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

+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

Re: Convert excel formula to SAS

Reeza — Thu, 12 May 2016 17:55:20 GMT

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

chennupriya — Thu, 12 May 2016 17:57:48 GMT

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

chennupriya — Thu, 12 May 2016 18:01:09 GMT

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

Reeza — Thu, 12 May 2016 18:04:20 GMT

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

Reeza — Thu, 12 May 2016 18:07:31 GMT

Use the Z3. format on the field.

format variable z3.;

Re: Convert excel formula to SAS

FreelanceReinh — Thu, 12 May 2016 18:18:44 GMT

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

Reeza — Thu, 12 May 2016 18:36:56 GMT

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

ballardw — Thu, 12 May 2016 18:50:53 GMT

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

FreelanceReinh — Thu, 12 May 2016 19:02:46 GMT

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

Re: Convert excel formula to SAS

chennupriya — Thu, 12 May 2016 20:09:28 GMT

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

FreelanceReinh — Thu, 12 May 2016 20:14:39 GMT

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

Re: Convert excel formula to SAS

Reeza — Thu, 12 May 2016 22:28:59 GMT

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.