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