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.
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.;
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.;
Thanks it worked perfectly!
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
Thanks Ksharp, like ballard your solution worked perfectly.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.