Help using Base SAS procedures

Convert "month_year" and "year_month" string to date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Convert "month_year" and "year_month" string to date

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.


Accepted Solutions
Solution
‎04-18-2014 11:05 AM
Super User
Posts: 11,343

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

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

View solution in original post


All Replies
Solution
‎04-18-2014 11:05 AM
Super User
Posts: 11,343

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

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

Occasional Contributor
Posts: 11

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

Thanks it worked perfectly!

Super User
Posts: 10,018

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

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

Occasional Contributor
Posts: 11

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

Thanks Ksharp, like ballard your solution worked perfectly.

Valued Guide
Posts: 2,177

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1373 views
  • 11 likes
  • 4 in conversation