topic Re: Replace character missing value (.) in SAS Programming

Replace character missing value (.)

Babloo — Tue, 16 Oct 2018 06:20:19 GMT

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

Re: Replace character missing value (.)

Kurt_Bremser — Tue, 16 Oct 2018 06:48:22 GMT

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;

Re: Replace character missing value (.)

Oligolas — Tue, 16 Oct 2018 06:57:20 GMT

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;

Re: Replace character missing value (.)

Oligolas — Tue, 16 Oct 2018 07:01:10 GMT

It is not, especially if you expect partial Dates 🙂

Re: Replace character missing value (.)

Patrick — Tue, 16 Oct 2018 07:04:15 GMT

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

Re: Replace character missing value (.)

Babloo — Tue, 16 Oct 2018 07:06:17 GMT

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

Re: Replace character missing value (.)

Kurt_Bremser — Tue, 16 Oct 2018 07:10:46 GMT

@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.

Re: Replace character missing value (.)

Kurt_Bremser — Tue, 16 Oct 2018 07:16:46 GMT

@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

Re: Replace character missing value (.)

Babloo — Tue, 16 Oct 2018 07:20:57 GMT

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

Re: Replace character missing value (.)

Kurt_Bremser — Tue, 16 Oct 2018 07:26:13 GMT

@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.

Re: Replace character missing value (.)

Babloo — Tue, 16 Oct 2018 08:02:17 GMT

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

Re: Replace character missing value (.)

andreas_lds — Tue, 16 Oct 2018 08:21:14 GMT

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

Re: Replace character missing value (.)

RW9 — Tue, 16 Oct 2018 08:56:29 GMT

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.

Re: Replace character missing value (.)

Kurt_Bremser — Tue, 16 Oct 2018 09:04:25 GMT

You have a value in DMY or MDY order, but use a YMD format/informat.

Please post the log of that whole step.

Re: Replace character missing value (.)

Oligolas — Wed, 17 Oct 2018 05:35:12 GMT

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.

Re: Replace character missing value (.)

RW9 — Wed, 17 Oct 2018 07:11:27 GMT

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.