data have;
infile cards ;
input ID month_dt
cards;
001 2017101
001 2017205
003 2018205
004 2018204
;
I have the above variable month_dt in best12 format, how can I change the format to mmddyy?
Thank you
data want;
set have;
year=floor(month_dt/1000);
month=mod(month_dt,100);
date=mdy(month,1,year);
format date date9.;
run;
The math above (using FLOOR and MOD) takes parts of your MONTH_DT integer and determines year and month. MDY turns year and month into an actual SAS date.
Assuming the 5th digit is not really useful (quarter?):
data want;
set have;
monthDate = mdy(mod(month_dt,100),1,floor(month_dt/1000));
format monthDate mmddyy.;
run;
proc print data=want; run;
So, month_dt is not a SAS date variable. It may look like it to humans, but to SAS it is just an integer. And so you have to use INPUT with the proper format to turn month_dt into a valid SAS date. But first you need to use PUT to turn it into a character string (because INPUT doesn't work on integers)
data have;
infile cards ;
input ID month_dt;
cards;
001 20170101
001 20170205
003 20180205
004 20180204
;
data want;
set have;
want_dt=input(put(month_dt,8.),yymmdd8.);
format want_dt date9.;
run;
@lillymaginta1 Please proofread, as you are using dates 2018204, which as a 7digit integer is actually meaningless and could be interpreted many ways. I have changed your 7 digit dates to 8 digit dates, otherwise this won't work. Please also specify which column is month and which is day, as your example doesn't make this clear, and I had to guess.
@lillymaginta1 wrote:
data have; infile cards ; input ID month_dt cards; 001 2017101 001 2017205 003 2018205 004 2018204 ;
I have the above variable month_dt in best12 format, how can I change the format to mmddyy?
Thank you
First thing is may need to explicitly state what date those values represent. Is 2017205 20 May 2017 or 5 Feb 2017 or 24 Jul 2017 (julian date)? .
Typically any of the year month day date appearance uses 2 digits for both the month and year to provide consistency. If your Month is only 1 digit then you need to state that and be prepared to deal with extra conditional coding because of poor structure of the data.
If the 2017205 is a Julian date then use the DATEJUL function to convert to a typical SAS date and apply desired format.
month_dt = datejul(month_dt); format month_dt mmddyy10.;
Thank you everyone for the suggestions and apologize for the lack of clarity. The date is entered as month year but for some reason there is extra digit to the right. The date is entered in the data as "2017101" and the type was set to Best12. None of the code above worked. Other dat entries include
2017410
2017309
2017412
2018205
I am not sure which digit should be removed before running any of the codes suggested above.
The date is entered as month year
I'm sorry but this clears nothing up. Saying month year indicates month is first, but your dates are 2017410, so the month is 20 and the year is 1741? Furthermore I specifically asked "Please also specify which column is month and which is day", not which is month and which is year.
Please explain this better. And we cannot work with 7 digit dates.
for the following number '2017410', 2017 would be the year and the month is the last two digits: 10. The number 4 is the quarter of the year so it needs to be dropped.
data want;
set have;
year=floor(month_dt/1000);
month=mod(month_dt,100);
date=mdy(month,1,year);
format date date9.;
run;
The math above (using FLOOR and MOD) takes parts of your MONTH_DT integer and determines year and month. MDY turns year and month into an actual SAS date.
@lillymaginta1 wrote:
for the following number '2017410', 2017 would be the year and the month is the last two digits: 10. The number 4 is the quarter of the year so it needs to be dropped.
Strongly suggest whipping with spaghetti noodles for whoever came up with that "brilliant" layout for displaying dates.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: