Replace character missing value (.)

[Babloo]

Appreciate if someone of you guide me to replace the character missing value '.' with date value. I tried the following program but it is not producing the desired output. I knew dot (.) is not a valid null value for character variable, but the real data in my project is like that only.

data test1;
length KNDEIND podverd $10.;
input KNDEIND $ podverd $;
datalines;
2017-11-25   2017-12-27
   .         2016-09-23     
run;


data want;
set test1;
if missing(KNDEIND) then KNDEIND=put(input(podverd,yymmdd10.)-1,yymmddd10.);
run;

Desired output:

KNDEIND	podverd
25/11/2017	27/12/2017
22/09/2016	23/09/2016

[Kurt_Bremser]

Storing dates as character is as stupid as it gets, because it deprives you of using all the nice features SAS provides with regards to dates and times.

Then the whole thing is done in one very simple step:

data test1;
input (kndeind podverd) (:yymmdd10.);
format kndeind podverd yymmddd10.;
if kndeind = . then kndeind = podverd - 1;
datalines;
2017-11-25   2017-12-27
   .         2016-09-23     
run;

[Oligolas]

It is not, especially if you expect partial Dates 🙂

[Kurt_Bremser]

---

@Oligolas wrote:

It is not, especially if you expect partial Dates 🙂

---

I said storing, not reading.

In the case of incomplete data, I read into a temporary character variable, and create complete data in the first step. Crappy data needs to be fixed/cleaned at the first point of entry into the data warehouse.

[Oligolas]

I am talking about storing. Especially in pharma one Encounters all the time partial Dates, this is every day practice and most of the time can not be cleaned.

[RW9]

But in pharma, you would be using the standard CDISC models, so SDTM would be ISO dates - can have partials, ADaM would have ISO and converted numeric date, time, datetime where possible to convert.

[Babloo]

Assume the values in those two variables are in character. Then how will
you achieve the desired result?

[Kurt_Bremser]

---

@Babloo wrote:
Assume the values in those two variables are in character. Then how will
you achieve the desired result?

---

I go to the dumb-ass who created that mess and apply a LART. Just kidding.

On second thought, no kidding.

For the data, I'd permanently convert it to a SAS date and be done with it. Most probably by fixing the defective code that's responsible for the character "dates" in the first place

[Oligolas]

You mean by converting the displayed format like this?

data want;
   set test1;
   if missing(KNDEIND) then KNDEIND=put(input(podverd,yymmdd10.)-1,ddmmyy10.);
   else KNDEIND=put(input(KNDEIND,yymmdd10.),ddmmyy10.);
   podverd=put(input(podverd,yymmdd10.),ddmmyy10.);
run;

[Patrick]

@Babloo

If you know that a dot in the character variable represents a missing then just test for a string with a dot in it.

data test1;
  length KNDEIND podverd $10.;
  input KNDEIND $ podverd $;
  datalines;
2017-11-25   2017-12-27
   .         2016-09-23     
;
run;

data want;
  set test1;
  if KNDEIND in (' ','.') then
    KNDEIND=put(input(podverd,yymmdd10.)-1,yymmddd10.);
run;

[Babloo]

I ran your code and it is not producing the desired output:(

[Kurt_Bremser · Accepted solution]

---

@Babloo wrote:

I ran your code and it is not producing the desired output:(

---

Can't be.

data test1;
  length KNDEIND podverd $10.;
  input KNDEIND $ podverd $;
  datalines;
2017-11-25   2017-12-27
   .         2016-09-23     
;
run;

data want;
  set test1;
  if KNDEIND in (' ','.') then
    KNDEIND=put(input(podverd,yymmdd10.)-1,yymmddd10.);
run;

proc print data=want noobs;
run;

(Exactly the code from @Patrick, witha proc print added)

Result:

 KNDEIND       podverd

2017-11-25    2017-12-27
2016-09-22    2016-09-23

Exactly matches your initial requirement.

If you want different formats, use them.

[Babloo]

I'm not certain why I'm unable to achieve this in DI studio. None of the missing values are converted into date value as defined in the code below. Under expression, I gave the code as follows. Am I missing something?

case when KNDEIND in (' ','.')  then put(input(PODVERD,yymmdd10.)-1,yymmddd10.) 
else KNDEIND 
end

My data is,

KNDEIND

01/01/2011

.

[andreas_lds]

So your "dates" changed from year-month-day to (maybe) day-month-year? You need to change the informat used to match your data!

[RW9]

Is your data really character?  Its just that having a dot there really looks like missing numeric.  In which case you don't need to be converting.

Also your logic is the wrong way round:

case when KNDEIND in (' ','.')  then

You don't want to convert if its missing!

Post some accurate test data in a working datastep, which reflects the data your are actually using, don't just type something in and assume.