BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

6 REPLIES 6
djbateman
Lapis Lazuli | Level 10

Ignore the /* in the code.

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

data_null__
Jade | Level 19

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

Howles
Quartz | Level 8

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

data_null__
Jade | Level 19

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

Howles
Quartz | Level 8

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

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
  • 6 replies
  • 7460 views
  • 3 likes
  • 4 in conversation