topic Converting Non-SAS Dates to SAS Dates in ODS and Base Reporting

Converting Non-SAS Dates to SAS Dates

djbateman — Tue, 02 Aug 2011 18:49:04 GMT

I have a text variable that has non-SAS date values; i.e. "8/7/2009 0:00:00".

I want to convert this into a SAS usable date: 07AUG2009.

I have been trying to cut up the value into segments using scan(date,1,"/") for the month, etc. in order to convert them into a usable string.

data dates_test;

set dates;

day=scan(date,2,"/");

monthnum=scan(date,1,"/");

year=substr(scan(date,3,"/"),1,4);

if monthnum=1 then monthname='JAN';

else if monthnum=2 then monthname='FEB';

else if monthnum=3 then monthname='MAR';

else if monthnum=4 then monthname='APR';

else if monthnum=5 then monthname='MAY';

else if monthnum=6 then monthname='JUN';

else if monthnum=7 then monthname='JUL';

else if monthnum=8 then monthname='AUG';

else if monthnum=9 then monthname='SEP';

else if monthnum=10 then monthname='OCT';

else if monthnum=11 then monthname='NOV';

else if monthnum=12 then monthname='DEC';

startdttxt=compress(day)||compress(monthname)||compress(year);

if length(startdttxt)=8 then startdt=input('0' || startdttxt,date9.);

else startdt=input(startdttxt,date9.);

keep date day monthnum monthname year startdttxt startdt;

format startdt date9.;

run;

Here are my questions:

1) Is there an easier way to do this conversion without cutting up the variable?

2) Wether there is an easier way or not, is there a way to convert the number 1 into January, 2 into February, etc.? I can convert January to 1, February to 2, etc. using the month() function, but not the other way around.

Converting Non-SAS Dates to SAS Dates

djbateman — Tue, 02 Aug 2011 18:50:05 GMT

Ignore the /* in the code.

Converting Non-SAS Dates to SAS Dates

sbb — Tue, 02 Aug 2011 19:01:09 GMT

To translate a numeric character into a represented month-name, use the MONNAME format in an assignment statement, along with a static 'day' and 'year' argument with the MDY function.

data _null_;

your_month = 2;

Month = put(mdy(your_month,1,year(today())),monname.);

putlog _all_;

run;

For the various INFORMATs to translate your date-time string, have a look at the SAS DOC - here is a reasonable Google advanced search argument to search the SAS-hosted documentation on its support website:

mm/dd/yyyy datetime informat site:sas.com

Scott Barry
SBBWorks, Inc.

Converting Non-SAS Dates to SAS Dates

data_null__ — Tue, 02 Aug 2011 19:01:26 GMT

There are easier methods...

322 data _null_;

323 x = "8/7/2009 0:00:00";

324 dt = input(x,mdyampm.);

325 put dt=datetime.;

326 date = datepart(dt);

327 put date=date9.;

328 run;

dt=07AUG09:00:00:00

date=07AUG2009

Converting Non-SAS Dates to SAS Dates

Howles — Wed, 03 Aug 2011 01:28:57 GMT

It seems to be even simpler:

data _null_ ;

x = "8/7/09 1:23:45" ;

date = input(x,mmddyy10.) ;

format date date9. ;

putlog _all_ ;

run ;

data_null_; wrote:
There are easier methods...
322  data _null_;
323     x = "8/7/2009 0:00:00";
324     dt = input(x,mdyampm.);
325     put dt=datetime.;
326     date = datepart(dt);
327     put date=date9.;
328     run;
  
dt=07AUG09:00:00:00
date=07AUG2009

Converting Non-SAS Dates to SAS Dates

data_null__ — Wed, 03 Aug 2011 03:20:20 GMT

I thought about that but didn't try it. Is the algorithm used by the informat documented?

Howles wrote:
It seems to be even simpler:
  
data _null_ ;
x = "8/7/09 1:23:45" ;
date = input(x,mmddyy10.) ;
format date date9. ;
putlog _all_ ;
run ;
data_null_; wrote:
There are easier methods...
322  data _null_;
323     x = "8/7/2009 0:00:00";
324     dt = input(x,mdyampm.);
325     put dt=datetime.;
326     date = datepart(dt);
327     put date=date9.;
328     run;
 
dt=07AUG09:00:00:00
date=07AUG2009

Re: Converting Non-SAS Dates to SAS Dates

Howles — Sat, 06 Aug 2011 01:57:57 GMT

I don't think it's documented, at least at

http://support.sas.com/documentation//cdl/en/leforinforref/63324/HTML/default/viewer.htm#n0mv0kngshd5sun1j5d38sbttwz1.htm

My theory is that once SAS has detected three delimiters, it knows enough to extract the MM, DD, and YY subfields and stops looking.

My test:

data _null_ ;

do x = "8/7/09 1:23:45"

, "8/7/09#1:23:45"

, "8/7/09J1:23:45"

, "8/7/09JJJ11111"

, "8/7/09JJJJ1111" ;

date = input(x,mmddyy10.) ;

format date date9. ;

putlog x= date= ;

end ;

_error_ = 0 ;

run ;

Result:

x=8/7/09 1:23:45 date=07AUG2009

x=8/7/09#1:23:45 date=07AUG2009

x=8/7/09J1:23:45 date=07AUG2009

x=8/7/09JJJ11111 date=07AUG2009

NOTE: Invalid argument to function INPUT at line 669 column 11.

x=8/7/09JJJJ1111 date=.

Seems that letters, not just special characters, work as delimiters. But there is evidence that SAS somehow cares about whatever comes after that third delimiter.

data_null_; wrote:
 I thought about that but didn't try it.  Is the algorithm used by the informat documented?
Howles wrote:
It seems to be even simpler:
data _null_ ;
x = "8/7/09 1:23:45" ;
date = input(x,mmddyy10.) ;
format date date9. ;
putlog _all_ ;
run ;
data_null_; wrote:
There are easier methods...
322  data _null_;
323     x = "8/7/2009 0:00:00";
324     dt = input(x,mdyampm.);
325     put dt=datetime.;
326     date = datepart(dt);
327     put date=date9.;
328     run;
  
dt=07AUG09:00:00:00
date=07AUG2009