Help using Base SAS procedures

Convert excel formula to SAS

Reply
Frequent Contributor
Posts: 120

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

Super User
Posts: 17,959

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.

Frequent Contributor
Posts: 120

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 ?

 

Super User
Posts: 17,959

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

Super User
Posts: 17,959

Re: Convert excel formula to SAS

Use the Z3. format on the field.

 

format variable z3.;

Frequent Contributor
Posts: 120

Re: Convert excel formula to SAS

[ Edited ]

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

Trusted Advisor
Posts: 1,115

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;
Super User
Posts: 17,959

Re: Convert excel formula to SAS

Hmm...as usual @FreelanceReinhard is correct Smiley Happy

 

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.  

Frequent Contributor
Posts: 120

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

Trusted Advisor
Posts: 1,115

Re: Convert excel formula to SAS

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

Super User
Posts: 17,959

Re: Convert excel formula to SAS

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. 

 

Super User
Posts: 10,550

Re: Convert excel formula to SAS

[ Edited ]

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.

Trusted Advisor
Posts: 1,115

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

Ask a Question
Discussion stats
  • 12 replies
  • 543 views
  • 6 likes
  • 4 in conversation