The SAS Output Delivery System and reporting techniques

Converting Non-SAS Dates to SAS Dates

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Converting Non-SAS Dates to SAS Dates

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.


Accepted Solutions
Solution
‎08-02-2011 03:01 PM
Respected Advisor
Posts: 3,777

Converting Non-SAS Dates to SAS Dates

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

View solution in original post


All Replies
Regular Contributor
Posts: 220

Converting Non-SAS Dates to SAS Dates

Ignore the /* in the code.

Super Contributor
Super Contributor
Posts: 3,174

Converting Non-SAS Dates to SAS Dates

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.

Solution
‎08-02-2011 03:01 PM
Respected Advisor
Posts: 3,777

Converting Non-SAS Dates to SAS Dates

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

Regular Contributor
Posts: 184

Converting Non-SAS Dates to SAS Dates

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

Respected Advisor
Posts: 3,777

Converting Non-SAS Dates to SAS Dates

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

Regular Contributor
Posts: 184

Re: Converting Non-SAS Dates to SAS Dates

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

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 4851 views
  • 3 likes
  • 4 in conversation