DATA Step, Macro, Functions and more

Formatting date to numeric

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

Formatting date to numeric

[ Edited ]

I wasn't able to find a way on how to convert the following date format to numeric:

27Oct2003

 

to

 

20031027

 

Any suggestions will be appreciated.


Accepted Solutions
Solution
‎06-18-2017 12:32 AM
Super User
Super User
Posts: 7,039

Re: Formatting date to numeric

To read a character string into a numeric date value use the INPUT function.

date = input(char_date,date9.);

You could then attach a format to it to have it appear in the format you specified.

format date yymmddn8.;

If instead you wanted to convert it to a number 19,840,930 that would look like a date in YYYYMMDD if printed without thousands seperators then you could use code like.

date_like_number = input(put(input(char_date,date9.),yymmddn8.),8.);
format date_like_number 8.;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Formatting date to numeric

What's your variable type/format from proc contents?

Frequent Contributor
Posts: 110

Re: Formatting date to numeric

[ Edited ]

@Reeza, the type is Char and the format (and informat) is $10. 

Frequent Contributor
Posts: 110

Re: Formatting date to numeric

[ Edited ]

 

                                            The CONTENTS Procedure:

  Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 

Acquiror_mgrno1Num8BEST. Acquiror_mgrno1
Acquiror_nameChar27$27.$27.Acquiror_name
AnnouncementChar9$9.$9.Announcement
Announcement_dateNum8BEST. Announcement_date
EffectiveChar9$9.$9.Effective
Effective_dateNum8BEST. Effective_date
Ex_post_dateChar10$10.$10.Ex_post_date
Merger_IDChar2$2.$2.Merger_ID
MgrnoChar8$8.$8.Mgrno
Note 1Char141$141.$141.Note 1
Note 2Char99$99.$99.Note 2
Note 3Char20$20.$20.Note 3
Note 4 Target:Char47$47.$47.Note 4 Target:
Note Ex PostChar174$174.$174.Note Ex Post
Target ends inChar10$10.$10.Target ends in
Target_mgrno1Num8BEST. Target_mgrno1
Target_nameChar29$29.$29.Target_name
Who continues?Char28$28.$28.Who continues?

 

Contributor
Posts: 65

Re: Formatting date to numeric

data have;
input date date9. ;
cards;
21oct2010
30sep2011
07jan2012
;
run;
data want; set have;
new_date=compress(day(date)||month(date)||year(date));
proc print;
run;

Contributor
Posts: 65

Re: Formatting date to numeric

[ Edited ]

The trick is to set the format of the date in the original database (date9.)

Super User
Posts: 3,250

Re: Formatting date to numeric

[ Edited ]

Your date is numeric to start with. Just apply the format you want:

 

data have;
input date date9. ;
format date yymmddn8.;
cards;
21oct2010
30sep2011
07jan2012
;
run;

proc print data = have;
run;
Frequent Contributor
Posts: 110

Re: Formatting date to numeric

[ Edited ]

@SASKiwi when I took your code to the actual data, I am getting the below error.

The code I have used: 

*This is what I have tried;
data IMPORT1;
	set IMPORT1;
	newdate = input(put(ex_post_date,DATE9.),yymmddn8.);
run;

*But I have also tried;
data IMPORT1;
  set IMPORT1;
  format ex_post_date date9.;
  newdate1=datepart(ex_post_date);
run;


This is the error:

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 61         
 62         data IMPORT1;
 63           set IMPORT1;
 64           format ex_post_date date9.;
                                  ______
                                  484
 NOTE 484-185: Format $DATE was not found or could not be loaded.
 
 65           newdate1=datepart(ex_post_date);
 66         run;
 
 NOTE: Character values have been converted to numeric 
       values at the places given by: (Line):(Column).
       65:21   
 NOTE: Invalid numeric data, Ex_post_date='30SEP1984' , at line 65 column 21.
Solution
‎06-18-2017 12:32 AM
Super User
Super User
Posts: 7,039

Re: Formatting date to numeric

To read a character string into a numeric date value use the INPUT function.

date = input(char_date,date9.);

You could then attach a format to it to have it appear in the format you specified.

format date yymmddn8.;

If instead you wanted to convert it to a number 19,840,930 that would look like a date in YYYYMMDD if printed without thousands seperators then you could use code like.

date_like_number = input(put(input(char_date,date9.),yymmddn8.),8.);
format date_like_number 8.;
Frequent Contributor
Posts: 110

Re: Formatting date to numeric

Thank @soumri, but I am getting the following error:

NOTE: Invalid numeric data, Ex_post_date='31MAR1996' , at line 64 column 25.
NOTE: Invalid numeric data, Ex_post_date='31MAR1996' , at line 64 column 46.
NOTE: Invalid numeric data, Ex_post_date='31MAR1996' , at line 64 column 65.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will
not be printed.
Acquiror_name=APP Acquiror_mgrno1=9999 Target_name=SBZ
Target_mgrno1=8888 Announcement_date=19941128 Effective_date=19970412
Announcement=28Nov1994 Effective=12Apr1997 Ex_post_date=31MAR1999 Merger_ID=AS
Who continues?=Acquiror Mgrno=49240 Target ends in=30SEP1996 Note Ex Post=
Note 1= Note 2= Note 3= Note 4 Target:= new_date=... _ERROR_=1 _N_=20
NOTE: Missing values were generated as a result of performing an operation on
missing values.
Each place is given by: (Number of times) at (Line):(Column).
60 at 64:20 60 at 64:40 60 at 64:61
NOTE: There were 60 observations read from the data set WORK.IMPORT1.
NOTE: The data set WORK.IMPORT1 has 60 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
PROC Star
Posts: 325

Re: Formatting date to numeric

is your date is currently numeric or in character

Frequent Contributor
Posts: 110

Re: Formatting date to numeric

Here is the information @kiranv_:

Format 

PROC Star
Posts: 325

Re: Formatting date to numeric

I have used the format given by @SASKiwi and used for char variable you have and it worked fine for me

data have;
informat ex_post_date $9.;
input ex_post_date $;
cards;
21oct2010
30sep2011
07jan2012
;
run;

data want(rename= (ex_post_date1= ex_post_date));
set have;
ex_post_date1 = input(ex_post_date,date9.);
drop ex_post_date;
format ex_post_date1 yymmddn8.;
 run;
Frequent Contributor
Posts: 110

Re: Formatting date to numeric

Thanks @kiranv_, your code worked very well. However, since @Tom's reply was first and his worked as well, I accepted his answer. Thank you very to you both for your helpful answers. 

Frequent Contributor
Posts: 110

Re: Formatting date to numeric

Hi again, @kiranv_@Tom, and @Reeza, I have just tried to use the new variable in an artimethic computation even though I have change the Char format to a Numeric format, but it did not work. Even if I change it to Best format, it still doesn't work. The reason why I have tried to change it again to Best is that there was another variables that is in the Best format and the computation worked well.

 

data have(rename= (ex_post_date1= ex_post_date));
	set have;
	ex_post_original=ex_post_date;
	ex_post_date1 = input(ex_post_date,date9.);
	drop ex_post_date;
	format ex_post_date1 yymmddn8.;
run;

data have;
	set have;
	numeric=put(ex_post_date1,best8.);
	format ex_post_date1 best8.;
run;


*Here is the math computation;
data want;
	set have;
	year=int(ex_post_date/10000);
	month=int(ex_post_date/100)-year*100;
	day=ex_post_date-year*10000-month*100;
	NEED_num=mdy(month,day,year);
	format NEED_num mmddyy10.;
	drop year month day;
run;
☑ This topic is solved.

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

Discussion stats
  • 20 replies
  • 259 views
  • 9 likes
  • 6 in conversation