05-12-2016 01:36 PM
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
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.
05-12-2016 01:57 PM
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 ?
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 .....
05-12-2016 02:18 PM
data want; set have; length Sales $4; if items='Pricing' then Sales=ifc(value>=0, cats('+', put(value, z3.)), put(value, z4.)); run;
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.
05-12-2016 04:09 PM
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
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.
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.
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.)