BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
klchtsk
Calcite | Level 5
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!
 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
klchtsk
Calcite | Level 5
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.
PaigeMiller
Diamond | Level 26

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.;
--
Paige Miller
klchtsk
Calcite | Level 5
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!
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
klchtsk
Calcite | Level 5
Thank you very much for the answer!
klchtsk
Calcite | Level 5
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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1970 views
  • 2 likes
  • 3 in conversation