- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi, I am seeking for help on formatting a date variable. Please see the example as below.
I would like to format the date1 variable from char $12. to num (length 8, final format DATETIME. and informat ANYDTDTM40). The solution I have thought about is firstly to remove the comma and make the date1 into 01Aug2018 format (then I know how to process further), but how can I achieve this first step (the underlined)?
Or, any other solutions/ suggestions?
Thank you very much. I really appreciate it.
/*1-raw data*/
data have;
input id date1 $12.;
datalines;
1 Aug 01, 2018
2 Aug 16, 2018
3 Jul 17, 2018
4 Sep 30, 2018
;
run;
/*2-solution?*/
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input id date1 $12.;
datalines;
1 Aug 01, 2018
2 Aug 16, 2018
3 Jul 17, 2018
4 Sep 30, 2018
;
run;
data want;
set have;
new_d=input(date1,anydtdte21.);
format new_d date9.;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You will need to create a new variable. DATE1 is already character, and can't be changed to numeric.
data want;
set have;
date1 = cat(scan(date1, 2, ', '). scan(date1, 1, ', '), scan(date1, 3, ', '));
date2 = input(date1, date9.);
format date2 date9.;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much! One typo after SCAN function - should be "," instead of "."
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input id date1 $12.;
datalines;
1 Aug 01, 2018
2 Aug 16, 2018
3 Jul 17, 2018
4 Sep 30, 2018
;
run;
data want;
set have;
new_d=input(date1,anydtdte21.);
format new_d date9.;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Why would you want to store values that are clearly DATE values into a DATETIME variable?
To do that you will need make up a timepart. Do you want to assume the timepart is zero (ie Midnight).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Somebody who uses R for the next step requires the datetime. format for date.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
In SAS a date is stored as the number of days and a datetime is stored as the number of seconds. You can convert from DATETIME to DATE using the DATEPART() function. You can convert from DATE to DATETIME use the DHMS() function (Days,Hours,Minutes,Seconds) by using the date value for the first argument and whatever time of day you want to use for the Hour, Minute and Second arguments. Normally you would use zeros.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the suggestions. It does not matter if R can handle it or not; it is just requirements.
So did you mean that I was not choosing a correct solution? But I did not see any error when transferring it back to DATE format, and so I chose it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@cpulala wrote:
Hi, I am seeking for help on formatting a date variable. Please see the example as below.
I would like to format the date1 variable from char $12. to num (length 8, final format DATETIME. and informat ANYDTDTM40). The solution I have thought about is firstly to remove the comma and make the date1 into 01Aug2018 format (then I know how to process further), but how can I achieve this first step (the underlined)?
Or, any other solutions/ suggestions?
Thank you very much. I really appreciate it.
/*1-raw data*/
data have;
input id date1 $12.;
datalines;
1 Aug 01, 2018
2 Aug 16, 2018
3 Jul 17, 2018
4 Sep 30, 2018
;
run;
/*2-solution?*/
Unless you have some other program examining informats specifically there is seldom a reason to force the informat assignment. If so, the ANDTDTM would be a last choice as it indicates you didn't know what format your data was going to come from.