DATA Step, Macro, Functions and more

Excel Formula in SAS Program

Reply
Contributor
Posts: 58

Excel Formula in SAS Program

I have formula in Excel as given below:

=IF(ISNUMBER(VALUE(N2)),TEXT(N2,"#.###"),N2)

 

 

How to covert this function in Excel  in SAS program

Super User
Super User
Posts: 9,227

Re: Excel Formula in SAS Program

And what have you tried?

if in excel = if in sas.

isnumber can be reversed as anyalpha.

the two different returns can be controlled by if /else if, or ifc or ifn.  

Without test data in the form of a datastep and what you want out at the end, that is as far as I can go.

want=ifc(anyalpha(value)=0,text,n2);
Super Contributor
Posts: 478

Re: Excel Formula in SAS Program

There are several function to accomplish the task, but choosing the right one depends on the data you have and your required output. Some times you may have to use multiple function to achieve it.

 

For Example: If you look at the code I'm using Compress function with "d" modifier to add all digits, "$" and "." to be removed. In this case you can find which variables values have only digits (Note: In case if you have some special symbols add them to list). Then MISSING() function to find a missing values (Here if missing then numeric values,else char values). INPUT to convert to numeric and PUT to convert to Character with the format you wish.

 

 

data have;
input char $;
datalines;
ABC1
$1
num2
5.55
55
;
run;
data want;
set have;
IF missing(compress(char,'$.','d')) then Char=PUT(INPUT(compress(char,'$','a'),8.),4.3);
run;

Hope this make sense. If not then provide some example data types you have.

 

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 2 replies
  • 87 views
  • 0 likes
  • 3 in conversation