Change date format and informat

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Change date format and informat

Hi everybody,

 

I have a dataset with a column of dates that looks like this:

 

Date

19940912

20031130

20130228

19960531

19990503

 

When I see the column attributes, the format is YYDDMMN8. and the informat 8.

 

I'm in the process of merging this dataset with another by date, and in the other dataset, the date has format and informat best12. and best32., respectively. The merging won't work, and I'm assuming it's because the date formats are different. How can I change the format and informat to match each other?

 

I googled and tried the following:

data want; set have;
    date_new=input(put(date,8.),best12.);
    attrib  date_new informat=best32.;
run;

 

and date_new looks like this:

 

date_new

12571

12906

16834

19590

 

 

Please help. How can I properly change the format and informat to best12. and best32.? Thanks a lot!


Accepted Solutions
Solution
‎11-01-2015 02:16 PM
Super User
Super User
Posts: 6,364

Re: Change date format and informat

[ Edited ]

SAS stores dates as number of days since 1960.  You can attach formats to have them appear as valid dates.

So in your first example where the values have the date format YYMMDDn8. then you have valid date values in the data.

date=1994-09-12 days=12673
date=2003-11-30 days=16039
date=2013-02-28 days=19417
date=1996-05-31 days=13300
date=1999-05-03 days=14367

Now your second dataset does NOT have a valid date format attached to the variable.  If the values still look they are dates in YYYYMMDD format then you will want to use the INPUT() function to convert those into actual date values.  So let's take your example list of values that look like valid dates displayed as the raw number of days and convert them to numbers that look like dates and then convert them to actual dates. 

data two;
   input days ;
   date_as_number = input(put(days,yymmddn8.),8.);
   date = input(put(date_as_number,8.),yymmdd8.);
   format date yymmdd10. ;
   put (_all_) (=);
cards;
12571
12906
16834
19590
;;;;

Here are the results:

days=12571 date_as_number=19940602 date=1994-06-02
days=12906 date_as_number=19950503 date=1995-05-03
days=16834 date_as_number=20060202 date=2006-02-02
days=19590 date_as_number=20130820 date=2013-08-20

Note that the values of DAYS and DATE are the same, they just look different in the output since they have different formats attached to them.  The value in DATE_AS_NUMBER might look like a date when printed, but the value it contains is not a date value.

 

So if you have a variable like the date_as_number values above then to convert it valid dates you just need to do the conversion as above and then attach the date format of your choice.  Since it is already a numeric variable you do not need to make a new variable.

mydate = input(put(mydate,8.),yymmdd8.);
format mydate date9. ;

Note that the INFORMAT attached to existing variable really does not have much value other than serving as documentation.  The INFORMAT is used for converting character strings into the stored value. So they are useful in an INPUT statement or and INPUT() function call.  They might have value if you use SAS/FSP, but who uses that product these days?

 

 

View solution in original post


All Replies
Regular Contributor
Posts: 161

Re: Change date format and informat

Would you be able to present a sample of the data from the second file ?

Kannan Deivasigamani
Solution
‎11-01-2015 02:16 PM
Super User
Super User
Posts: 6,364

Re: Change date format and informat

[ Edited ]

SAS stores dates as number of days since 1960.  You can attach formats to have them appear as valid dates.

So in your first example where the values have the date format YYMMDDn8. then you have valid date values in the data.

date=1994-09-12 days=12673
date=2003-11-30 days=16039
date=2013-02-28 days=19417
date=1996-05-31 days=13300
date=1999-05-03 days=14367

Now your second dataset does NOT have a valid date format attached to the variable.  If the values still look they are dates in YYYYMMDD format then you will want to use the INPUT() function to convert those into actual date values.  So let's take your example list of values that look like valid dates displayed as the raw number of days and convert them to numbers that look like dates and then convert them to actual dates. 

data two;
   input days ;
   date_as_number = input(put(days,yymmddn8.),8.);
   date = input(put(date_as_number,8.),yymmdd8.);
   format date yymmdd10. ;
   put (_all_) (=);
cards;
12571
12906
16834
19590
;;;;

Here are the results:

days=12571 date_as_number=19940602 date=1994-06-02
days=12906 date_as_number=19950503 date=1995-05-03
days=16834 date_as_number=20060202 date=2006-02-02
days=19590 date_as_number=20130820 date=2013-08-20

Note that the values of DAYS and DATE are the same, they just look different in the output since they have different formats attached to them.  The value in DATE_AS_NUMBER might look like a date when printed, but the value it contains is not a date value.

 

So if you have a variable like the date_as_number values above then to convert it valid dates you just need to do the conversion as above and then attach the date format of your choice.  Since it is already a numeric variable you do not need to make a new variable.

mydate = input(put(mydate,8.),yymmdd8.);
format mydate date9. ;

Note that the INFORMAT attached to existing variable really does not have much value other than serving as documentation.  The INFORMAT is used for converting character strings into the stored value. So they are useful in an INPUT statement or and INPUT() function call.  They might have value if you use SAS/FSP, but who uses that product these days?

 

 

Contributor
Posts: 45

Re: Change date format and informat

Thanks a lot! I ended up changing the format of date_as_numbers to real dates by doing the following:

 

newdate = input(put(date,8.),yymmdd8.);

format newdate yymmddn8. ;

 

And datasets merged! Cheers!

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 2208 views
  • 1 like
  • 3 in conversation