topic Re: How to convert date from $9. to ddmmyy8. ? in SAS Programming

How to convert date from $9. to ddmmyy8. ?

J111 — Mon, 31 Oct 2022 10:09:47 GMT

Hello,

What is the best way to convert Have data to Want data ?

From 30OCT2021 to 301021 without informat as 30OCT2021 is actually created during a data step

Thanks

-----------------------------------------------------------------
Data Have ;
input x9 $9. ;
cards ;
30OCT2021
3NOV2022
;
Run ;

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

Data Want ;
input x10 ddmmyy8. ;
format x10 ddmmyy8. ;
cards ;
30102021
03112022
;
run ;

Re: How to convert date from $9. to ddmmyy8. ?

PeterClemmensen — Mon, 31 Oct 2022 10:52:12 GMT

I'm not sure what you mean by 'without informat'?

Anyways, here is probably the easiest way

Data Have ;
input x9 $9. ;
cards ;
30OCT2021
3NOV2022
;
Run ;


data want;
   set have;
   x10 = input(x9, date9.);
   format x10 ddmmyyn8.;
run;

Re: How to convert date from $9. to ddmmyy8. ?

J111 — Mon, 31 Oct 2022 11:16:21 GMT

In other words - how to create a date from the variable NEW ?

data log_analysis ;
length Date_D 8 Date_M_ $3 Date_Y 4 ;
infile "/logs/ABC.log" end = EOF truncover ;
input ;
if index(_infile_, 'The SAS System') > 0 then Date_M = scan(_infile_,7) ;
DATE_M_ = substr(DATE_M,1,3) ;
if index(_infile_, 'The SAS System') > 0 then Date_D = scan(_infile_,8) ;
if index(_infile_, 'The SAS System') > 0 then Date_Y = scan(_infile_,9) ;
NEW = compress(Date_D||Date_M_||Date_Y) ;
run ;

Re: How to convert date from $9. to ddmmyy8. ?

J111 — Mon, 31 Oct 2022 11:28:49 GMT

Hoping for a shortcut to this:

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

data log_analysis ;
length Date_D 8 Date_M_ $3 Date_Y 4 ;
infile "/opt/sas/prod_logs/BOI_SITE_INTRNL_STD_2022.10.31_07.06.21.log" end = EOF truncover ;
input ;
if index(_infile_, 'The SAS System') > 0 then Date_M = scan(_infile_,7) ;
DATE_M_ = substr(DATE_M,1,3) ;

if DATE_M_ = 'Jan' then month = 1 ;
else if DATE_M_ = 'Feb' then month = 2 ;
else if DATE_M_ = 'Mar' then month = 3 ;
else if DATE_M_ = 'Apr' then month = 4 ;
else if DATE_M_ = 'May' then month = 5 ;
else if DATE_M_ = 'Jun' then month = 6 ;
else if DATE_M_ = 'Jul' then month = 7 ;
else if DATE_M_ = 'Aug' then month = 8 ;
else if DATE_M_ = 'Sep' then month = 9 ;
else if DATE_M_ = 'Oct' then month = 10 ;
else if DATE_M_ = 'Nov' then month = 11 ;
else if DATE_M_ = 'Dec' then month = 12 ;

if index(_infile_, 'The SAS System') > 0 then Date_D = scan(_infile_,8) ;
if index(_infile_, 'The SAS System') > 0 then Date_Y = scan(_infile_,9) ;
Date_ = MDY(Month,Date_D,Date_Y) ;
format date_ ddmmyy8. ;
run ;

Re: How to convert date from $9. to ddmmyy8. ?

PeterClemmensen — Mon, 31 Oct 2022 11:32:08 GMT

Why do you want to complicate things like this?

Re: How to convert date from $9. to ddmmyy8. ?

PaigeMiller — Mon, 31 Oct 2022 11:57:11 GMT

agreeing with @PeterClemmensen , you seem to be taking an unnecessarily complicated path. Why do you need months as numbers? Is it to make sure they sort in the proper order, rather than alphabetical order? Or some other reason, please specify.

DATE_M_ = substr(DATE_M,1,3) ;

We don't know what the value of DATE_M is, you haven't told us. However, since the first 3 characters of DATE_M could be 'JAN', then how about this:

date_m_= input(cats('01',substr(date_m,1,3),'2022'),date9.);
format date_m_ monname3.;

Now you have a numeric SAS date value (example JAN) that appears to humans as JAN. So when you do the sorting of this numeric SAS date value, JAN is the first month, FEB is the second month, and so on.

ADVICE: if you ever wind up with character strings that represent dates, convert them to numeric at the first opportunity using the INPUT function and the proper informat. DO NOT — I repeat DO NOT — try to pull "date" character strings apart and combine them back together as character strings. Work with numeric dates, SAS has already done the hard work to create functions, formats and informats that do all the hard work of handling dates. In addition, numeric values for dates will sort in numeric order, with JAN sorting before APR, whereas if you leave variables as character, they will sort alphabetically. Better yet, do not create dates as character strings in the first place, create them as numeric SAS date values.

Re: How to convert date from $9. to ddmmyy8. ?

J111 — Mon, 31 Oct 2022 12:51:59 GMT

cats function is very help full - many thanks

Re: How to convert date from $9. to ddmmyy8. ?

PaigeMiller — Mon, 31 Oct 2022 13:05:05 GMT

@J111

Please unmark your own response as correct.

If you found my response to be the correct answer, mark that response as correct.