SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to change cells value to different with IF function?

Reply
Occasional Contributor
Posts: 6

How to change cells value to different with IF function?

Hello, I have some problem with my SAS Smiley Wink

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

Super User
Posts: 5,424

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

Why have you chosen the if() functions specifically?

What programming environment are you using?

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

Data never sleeps
Occasional Contributor
Posts: 6

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

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

Super User
Posts: 19,767

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

Using the coalesce function would be as follows:

coalesce(enddate,150108)

Super User
Posts: 10,018

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

if missing(enddate) then enddate=1501018 ;

Occasional Contributor
Posts: 6

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

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

Super User
Posts: 5,424

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

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)

Data never sleeps
Super User
Super User
Posts: 7,035

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

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;

Frequent Contributor
Posts: 115

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

*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;

Occasional Contributor
Posts: 6

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

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

Ask a Question
Discussion stats
  • 9 replies
  • 582 views
  • 0 likes
  • 7 in conversation