BookmarkSubscribeRSS Feed
jarr25
Fluorite | Level 6

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

9 REPLIES 9
LinusH
Tourmaline | Level 20

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
jarr25
Fluorite | Level 6

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

Reeza
Super User

Using the coalesce function would be as follows:

coalesce(enddate,150108)

Ksharp
Super User

if missing(enddate) then enddate=1501018 ;

jarr25
Fluorite | Level 6

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

LinusH
Tourmaline | Level 20

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

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;

naveen_srini
Quartz | Level 8

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

Shirki
Calcite | Level 5

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

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 2208 views
  • 0 likes
  • 7 in conversation