topic Re: How to change cells value to different with IF function? in SAS Data Management

How to change cells value to different with IF function?

jarr25 — Sun, 18 Jan 2015 12:04:31 GMT

Hello, I have some problem with my SAS

What I'm trying to do, it's basically changing column value with function IF.

So i have that type of column:

What I need, is that blank cells (dots) to be filled with text 1501018, if there text in it already, it should leave it.

Could you explain me how to do that, please?

I'm trying to do it with IF function, but I have some problems with it..

It would be:

IF(/*expr1,expr2,expr3*/)

changed to

IF(/*ENDDATE = .. ?

Re: How to change cells value to different with IF function?

LinusH — Sun, 18 Jan 2015 12:20:50 GMT

Why have you chosen the if() functions specifically?

What programming environment are you using?

The coalesce() function would probably be a better fit.

Re: How to change cells value to different with IF function?

jarr25 — Sun, 18 Jan 2015 12:29:09 GMT

Just first which crossed my mind - it's often in use in programming

I'm using Data Integration Studio

could you tell me which one is a proper one for me and how to fill that function?

COALESCE(/*any-type, any-union-compatible-type*/)

COALESCE(expr [, expr ]...)

COALESCE( scalar_expression,scalar_expression,...)

COALESCE(<value>)

SELECT COALESCE ( <expression1>, <exprression2>, ...[exprN] )

Re: How to change cells value to different with IF function?

Ksharp — Sun, 18 Jan 2015 13:06:39 GMT

if missing(enddate) then enddate=1501018 ;

Re: How to change cells value to different with IF function?

jarr25 — Sun, 18 Jan 2015 15:18:10 GMT

That's not working for me, would you have other ideas?

Re: How to change cells value to different with IF function?

Tom — Sun, 18 Jan 2015 15:30:47 GMT

Looks like you have a numeric variable that is formatted as a DATETIME value.

The value you want to display does not look like a date time since if it was it would be just before 9AM on Jan 18th, 1960.

You cannot put text into a numeric variable.

You could create a custom format.

proc format ;

value enddate

. = '1501018'

other = [DATETIME20.];

run;

Re: How to change cells value to different with IF function?

naveen_srini — Sun, 18 Jan 2015 15:49:24 GMT

*Assuming your enddate has datetime values, so covert the variable value to character and replace missing with your given character constant;

data have;/*test dataset*/

enddate='18jan2015:11:43:20'dt;

length new_enddate $19 ;

new_enddate=put(enddate,is8601dt.);

do i =1 to 5;

if i>2 then new_enddate=" ";

output;

end;

run;

data want(drop=i);

set have;

if missing(new_enddate) then new_enddate="1501018";

format enddate datetime18.;

run;

Re: How to change cells value to different with IF function?

Reeza — Sun, 18 Jan 2015 19:10:07 GMT

Using the coalesce function would be as follows:

coalesce(enddate,150108)

Re: How to change cells value to different with IF function?

LinusH — Sun, 18 Jan 2015 21:19:30 GMT

DI Studio, which transformation?

And how does it not work?

And what kind of value is 150108 anyway? It does not make sense as a datetime value (02JAN60:17:41:48).

Do you refer to 8th ofj fanuary 2015?

coalesce(enddate, "8Jan2015:00:00:"dt)

Re: How to change cells value to different with IF function?

Shirki — Thu, 22 Jan 2015 04:19:40 GMT

if you are using a DI join transformation, you may need to use a "case when" statement.

ie:

case when enddate = . then 1501018 else enddate end

although you might need to sort out your formats