BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JOLSAS
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
kannand
Lapis Lazuli | Level 10

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

Kannan Deivasigamani
Tom
Super User Tom
Super User

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?

 

 

JOLSAS
Quartz | Level 8

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 75121 views
  • 2 likes
  • 3 in conversation