topic Formatting date to numeric in SAS Programming

Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:16:31 GMT

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

27Oct2003

20031027

Any suggestions will be appreciated.

Re: Formatting date to numeric

Reeza — Sun, 18 Jun 2017 01:03:00 GMT

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

Re: Formatting date to numeric

soumri — Sun, 18 Jun 2017 01:22:38 GMT

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;

Re: Formatting date to numeric

soumri — Sun, 18 Jun 2017 01:27:33 GMT

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

Re: Formatting date to numeric

SASKiwi — Sun, 18 Jun 2017 01:51:29 GMT

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;

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:16:15 GMT

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

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:13:06 GMT

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

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:12:17 GMT

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

Re: Formatting date to numeric

kiranv_ — Sun, 18 Jun 2017 04:15:33 GMT

is your date is currently numeric or in character

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:21:07 GMT

Here is the information @kiranv_:

Re: Formatting date to numeric

Tom — Sun, 18 Jun 2017 04:24:01 GMT

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

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:35:55 GMT

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?

Re: Formatting date to numeric

kiranv_ — Sun, 18 Jun 2017 04:30:30 GMT

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;

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 04:33:30 GMT

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.

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 06:02:06 GMT

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;

Re: Formatting date to numeric

kiranv_ — Sun, 18 Jun 2017 06:39:00 GMT

the way date is stored in SAS as that it starts from 1/1/1960. So it is not fitting in your calculation. So when you are calculating with date it does not work. below thing may work

 
 data want;
 set want;
 numeric=put(ex_post_date,best8.);
run;

 
 data wantfinal;
 set want;
 year=year(numeric);
 month=month(numeric);
 day=day(numeric);
 NEED_num=mdy(month,day,year);
 format NEED_num mmddyy10.;
 drop year month day;
run;

Re: Formatting date to numeric

Reeza — Sun, 18 Jun 2017 06:42:15 GMT

Please review how SAS stores date variables. It counts it as the number of days. So that num_needed is actually what you got from the INPUT() from @Toms answer. If you want day/month/year, use those respective functions on the date.

Please run the following and then look at the proc contents of the data set, types/formats.

Data sample;
D1 = '01Jan2009';* -> character;

D2 = input(D1, DATE9.); *SAS date;
D3 = D2; *SAS date, formatted;

Format D3 ddmmyy10.;

M = month(d2);
D = day(d2);
Y = year(d2);

Run;

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 07:06:49 GMT

@Reeza, thanks for this helpful suggestion. The code works very well. You are right in regards to being careful how SAS stores date variables. I will look into it now.

Thanks again.

Re: Formatting date to numeric

Yegen — Sun, 18 Jun 2017 07:09:10 GMT

@kiranv_, thanks for your reply. The code didn't work, unfortunately, work. However, @Reeza's suggestion works well.

Thanks to you both again.

Re: Formatting date to numeric

Tom — Sun, 18 Jun 2017 12:20:54 GMT

You are still not getting the difference between a character variable and a numeric variable and the meaning of a format. The format is just instructions on how to display value. The PUT() function will always return character strings. Then INPUT() function can return either numeric or character values based on the type of INFORMAT used as the second argument. There is a BEST format that will try to fit a number into the width specified. THere is isn't really a BEST informat, but if you use it then SAS will silently convert to using the normal numeric informat which handles and values that the BEST format would write.

Let's rewrite your posted code using variable names that will help understand what they contain.

So let's start with a character variable that has strings that look like dates in DATE9 format. We can use the INPUT() function to convert that to a date. We can make copies of it into other variables so we can apply different formats to them to see how they print. We could use an INPUT(PUT()) sandwich to convert the date to a number with the days in the ones and tens places. We can use the MONTH(),DAY() and YEAR() functions to convert a date value into its parts. Or we could use arthimetic to convert a number that is in the format YY,YYM,MDD into the year, month and day values. We can use the MDY() function to generate a date value from month day and year values.

data have ;
  input char_date $9.;
cards;
01JAN1960
14JUN2017
;

data want ;
  set have (keep=char_date);
  date1 = input(char_date,date9.);
  date2 = date1;
  date_unformatted=date1;
  format date1 date9. date2 yymmddn8.;
  number = input(put(date1,yymmddn8.),8.);
  year1=year(date1);
  month1=month(date1);
  day1=day(date1);
  year2=int(number/10000);
  month2=int(mod(number,10000)/100);
  day2=mod(number,100);
  new_date=mdy(month1,day1,year1);
  format char_date $quote. ;
  format date1 date9. date2 yymmddn8. new_date mmddyy10.;
  format number date_unformatted comma10.;
run;

I purposely using the $QUOTE and COMMA formats so that when you print the values you can tell which variables are character and non-date numbers.