topic Re: convert variable from best12 to date in SAS Programming

convert variable from best12 to date

lillymaginta1 — Thu, 27 Jan 2022 18:39:18 GMT

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

Re: convert variable from best12 to date

PGStats — Thu, 27 Jan 2022 19:13:01 GMT

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;

Re: convert variable from best12 to date

PaigeMiller — Thu, 27 Jan 2022 19:28:51 GMT

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.

Re: convert variable from best12 to date

ballardw — Thu, 27 Jan 2022 19:13:40 GMT

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

Re: convert variable from best12 to date

Reeza — Thu, 27 Jan 2022 19:39:50 GMT

2018204 -> what is the interpretation of that value to a date?

2018-2-04?
2018-20(day) -4Month(?)

Re: convert variable from best12 to date

lillymaginta1 — Fri, 28 Jan 2022 13:39:14 GMT

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.

Re: convert variable from best12 to date

PaigeMiller — Fri, 28 Jan 2022 13:54:53 GMT

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.

Re: convert variable from best12 to date

lillymaginta1 — Fri, 28 Jan 2022 14:01:47 GMT

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.

Re: convert variable from best12 to date

PaigeMiller — Fri, 28 Jan 2022 14:14:22 GMT

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.

Re: convert variable from best12 to date

lillymaginta1 — Fri, 28 Jan 2022 14:17:22 GMT

Thank you very much!

Re: convert variable from best12 to date

ballardw — Fri, 28 Jan 2022 15:34:39 GMT

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