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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

20 REPLIES 20
Reeza
Super User

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

Yegen
Pyrite | Level 9

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

Yegen
Pyrite | Level 9

 

                                            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?

 

soumri
Quartz | Level 8

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;

soumri
Quartz | Level 8

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

SASKiwi
PROC Star

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;
Yegen
Pyrite | Level 9

@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.
Tom
Super User Tom
Super User

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.;
Yegen
Pyrite | Level 9

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
kiranv_
Rhodochrosite | Level 12

is your date is currently numeric or in character

Yegen
Pyrite | Level 9

Here is the information @kiranv_:

Format 

kiranv_
Rhodochrosite | Level 12

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;
Yegen
Pyrite | Level 9

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. 

Yegen
Pyrite | Level 9

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;

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
  • 20 replies
  • 61168 views
  • 11 likes
  • 6 in conversation