Convert "month_year" and "year_month" string to date

[#1]

Hi everyone

I have a string like "042014" and other like "201404" both meaning April 2014.

What I want to do is to convert both ones in some "Month-Year" format, so I can use them in IF-THEN-ELSE statement like > < = etc.

In addition: I have imported them in $char6. I do not know if I can import them in date format, like DDMMYY10. because in that case I do not have the days.

Thanks and regards from Brazil.

[ballardw · Accepted solution]

If you are getting values as described I don't think you'll ever find an informat that would read them accurately because of multiple possible interpretations.

You will have to parse the data to some extent and determine which have the leading year and which are following. If the years in question are always 4 digits you might be able to use something like:

if substr(datestring,1,2) in ('19','20') then do; /* if the first 2 characters are 19 or 20 then they can't be months so should be year*/

/* assuming you want a date value you need to assume a day of month, here I use the first day of the month*/

     monyear = mdy(input(substr(datestring,5,2),f2.),1,input(substr(datestring,1,4),f4.));

end;

else do;

     monyear = mdy(input(substr(datestring,1,2),f2.),1,input(substr(datestring,3,4),f4.));

end;

format monyear mmyy6.;

[#3]

Thanks it worked perfectly!

[Ksharp]

Same like ballard:

data x;
input date $6.;
cards;
201404
042014
;
run;
data x;
 set x;
 if date in: ('20' '19') then d=input(date||'01',yymmdd10.);
  else d=input('01'||date,ddmmyy10.);
  format d date.;
run;

Xia Keshan

[#5]

Thanks Ksharp, like ballard your solution worked perfectly.

[Peter_C]

I would like to suggest that a user informat would make the code look simpler.

based on the first character of the string, you have a good clue about the more appropriate INFORMAT.

This offers an opportunity to demonstrate using informats in the LABEL area of user defined informats.

PROC FORMAT ;

INVALUE mixed_up_date

       '010000' - '199999' = [mmyyn6.] /* see below */

       '200000' - '299999' = [yymmn6.]

   other = _error_ ;

run ;

proc format

run ;

data x ;

attrib  date format=date9. informat= mixed_up_date6. ;

input date ;

cards ;

201404

042014

failure

;

Unfortunately, although there is an informat for YYYYMM style dates, there is no informat for MMYYYY

Well, not until someone creates one.

Until SAS Institute do (is a "birdie" listening? ), here is mine (date ranges 2000 to 2100)

data cntlin ;

retain fmtname 'mmyyN'  type 'i ' ;

do mm=1 to 12 ;

do yy = 2000 to 2100 ;

start = put( mm*1e4 + yy, z6. ) ;

label = mdy( mm, 1, yy ) ;

output ;

end; end ;

hlo= 'o' ;

call missing( start, label ) ;

output ;

run ;

PROC FORMAT cntlin = cntlin ;

run  ;

Message was edited by: Peter Crawford  (uk time 20:54 22Apr2014)    someone needed to fix my defects = bugs caused by rushed and untested typing.   The corrected code has been tested in SAS9.3 and 9.4