BookmarkSubscribeRSS Feed
chennupriya
Quartz | Level 8

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
Reeza
Super User

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
Quartz | Level 8

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
Super User

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

Reeza
Super User

Use the Z3. format on the field.

 

format variable z3.;

chennupriya
Quartz | Level 8

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
Jade | Level 19

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
Super User

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
Quartz | Level 8

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
Jade | Level 19

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

Reeza
Super User

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
Super User

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
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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