topic Re: Convert "month_year" and "year_month" string to date in SAS Procedures

Convert "month_year" and "year_month" string to date

anonymous_user — Fri, 18 Apr 2014 14:30:55 GMT

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.

Re: Convert "month_year" and "year_month" string to date

ballardw — Fri, 18 Apr 2014 15:05:30 GMT

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

Re: Convert "month_year" and "year_month" string to date

Ksharp — Sat, 19 Apr 2014 01:50:42 GMT

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

Re: Convert "month_year" and "year_month" string to date

anonymous_user — Mon, 21 Apr 2014 18:15:31 GMT

Thanks it worked perfectly!

Re: Convert "month_year" and "year_month" string to date

anonymous_user — Mon, 21 Apr 2014 18:17:49 GMT

Thanks Ksharp, like ballard your solution worked perfectly.

Re: Convert "month_year" and "year_month" string to date

Peter_C — Tue, 22 Apr 2014 13:44:19 GMT

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