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.
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.;
What's your variable type/format from proc contents?
@Reeza, the type is Char and the format (and informat) is $10.
The CONTENTS Procedure:
Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label
Acquiror_mgrno1 | Num | 8 | BEST. | Acquiror_mgrno1 | |
Acquiror_name | Char | 27 | $27. | $27. | Acquiror_name |
Announcement | Char | 9 | $9. | $9. | Announcement |
Announcement_date | Num | 8 | BEST. | Announcement_date | |
Effective | Char | 9 | $9. | $9. | Effective |
Effective_date | Num | 8 | BEST. | Effective_date | |
Ex_post_date | Char | 10 | $10. | $10. | Ex_post_date |
Merger_ID | Char | 2 | $2. | $2. | Merger_ID |
Mgrno | Char | 8 | $8. | $8. | Mgrno |
Note 1 | Char | 141 | $141. | $141. | Note 1 |
Note 2 | Char | 99 | $99. | $99. | Note 2 |
Note 3 | Char | 20 | $20. | $20. | Note 3 |
Note 4 Target: | Char | 47 | $47. | $47. | Note 4 Target: |
Note Ex Post | Char | 174 | $174. | $174. | Note Ex Post |
Target ends in | Char | 10 | $10. | $10. | Target ends in |
Target_mgrno1 | Num | 8 | BEST. | Target_mgrno1 | |
Target_name | Char | 29 | $29. | $29. | Target_name |
Who continues? | Char | 28 | $28. | $28. | Who continues? |
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;
The trick is to set the format of the date in the original database (date9.)
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;
@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.
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.;
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
is your date is currently numeric or in character
Here is the information @kiranv_:
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.