topic Re: More Elegant Way To Replace UNK and UN In Date Values in SAS Programming

More Elegant Way To Replace UNK and UN In Date Values

klchtsk — Sat, 09 Apr 2022 16:11:09 GMT

Dear all,
I'm working with the data, which contains dates with missing either month (replaced with UNK) or day (replaced with UN).
UPD: the values look like this: 2022JAN15, 2022JANUN and 2022UNKUN.
I have seen multiple posts on the same topic, but I'm still in pursue of the most elegant solutions.
 
I have made it following:
 
 
data want;
set have;
date_new=tranwrd(tranwrd(upcase(date), 'UNK', 'JAN'), 'UN', '01');
run;
I wonder if it's possible to make with PRXCHNAGE:
 
data want;
set have;
date_new=prxchange('s/(\d{4})((?(?=U)UNK|(\w{3})))((?(?=U)UN|(\d{2})))/$1 $3 $5/i',
-1, AE.aestdat);
run;
But it fails to put JAN or 01, if the condition is met. Replace portions of the PRXCHANGE doesn't accept " | " as OR.
Thank you in advance for your help and advice!
 
 
 

Re: More Elegant Way To Replace UNK and UN In Date Values

PaigeMiller — Sat, 09 Apr 2022 16:09:14 GMT

Fundamentally, I think you want to convert the character string dates to actual numeric SAS dates, rather than make them character strings that look like dates.

But since you don't give us examples of the input data, I can't be more specific.

Re: More Elegant Way To Replace UNK and UN In Date Values

klchtsk — Sat, 09 Apr 2022 16:12:08 GMT

Thank you for your reply! Sorry for not putting it in a first place. I have added an update to the original message. The values look like this: 2022JAN15, 2022JANUN and 2022UNKUN.

Re: More Elegant Way To Replace UNK and UN In Date Values

PaigeMiller — Sat, 09 Apr 2022 19:32:21 GMT

I would steal your earlier code, and modify it

date_new=input(tranwrd(tranwrd(upcase(date), 'UNK', 'JAN'), 'UN', '01'),anydtdte.);
format date_new date9.;

Re: More Elegant Way To Replace UNK and UN In Date Values

klchtsk — Sat, 09 Apr 2022 19:41:49 GMT

Thank you for your reply! I'm pleased that you have used my code! Any chance PRXCHANGE can be make it in one run without double TRANWRD? I'm just mesmerized by this function! Thank you!

Re: More Elegant Way To Replace UNK and UN In Date Values

PaigeMiller — Sat, 09 Apr 2022 20:34:03 GMT

I'm sure the prschange experts can advise, but I am not one of them.

Those are odd dates, with year, then month, then day.

Re: More Elegant Way To Replace UNK and UN In Date Values

mkeintz — Sat, 09 Apr 2022 23:09:07 GMT

The nested TRANWRDs put forth by the OP would improperly process "JUN" in the month component, so text like

2001JUN15 would be converted to 2001J0115 prior to a failed attempt to convert it to a date value, as below:

data _null_;
  do date='2001FEBUN','2001UNKUN','2001UNK15','2001MAR15',  '2001JUN15';
    date_new=input(tranwrd(tranwrd(upcase(date), 'UNK', 'JAN'), 'UN', '01'),anydtdte.);
    format date_new date9.;
    put (_all_) (=);
  end;
run;

But append a "!" to the DATE text, allows TRANWRD "UN!" to "01!", which avoids the "JUN" problem:

data _null_;
  do date='2001FEBUN','2001UNKUN','2001UNK15','2001MAR15',  '2001JUN15';
    date_new=input(tranwrd(tranwrd(upcase(cats(date,'!')), 'UNK', 'JAN'), 'UN!', '01!'),anydtdte.);
    format date_new date9.;
    put (_all_) (=);
  end;
run;

Re: More Elegant Way To Replace UNK and UN In Date Values

klchtsk — Mon, 11 Apr 2022 18:50:24 GMT

Thank you very much for the answer!

Re: More Elegant Way To Replace UNK and UN In Date Values

klchtsk — Mon, 11 Apr 2022 18:51:52 GMT

You are completely right, that I have strayed further from the whole point of data transformation. I should focus of calculation of intervals using the data I have but not on transformation I do not need!