Desktop productivity for business analysts and programmers

Excel-date format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Excel-date format

Hi everyone,

How can I read this type of dates in eGuide?

Tuesday, 11th June 1985

Monday, 4th June 1990


Thx!

David


Accepted Solutions
Solution
‎05-13-2013 07:57 AM
Occasional Contributor
Posts: 12

Re: Excel-date format

Tis fixed the problem:

data test;

F5='Friday, 10th January 1986';

worddate=left(scan(F5,2,','));

day=substr(worddate,1,2);

monthname=scan(worddate,2, ' ');

month=substr(monthname,1,3);

year=scan(worddate,3,' ');

date9=input(catt(day,month,year),date9.);

run;

proc print ; run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,050

Re: Excel-date format

I don't think you'll be able to read these without doing some further processing. Are they always in the form

<weekday>, <date>th <month> <year>

If they are, it is very easy to reformat them to be converted.

Tom

Solution
‎05-13-2013 07:57 AM
Occasional Contributor
Posts: 12

Re: Excel-date format

Tis fixed the problem:

data test;

F5='Friday, 10th January 1986';

worddate=left(scan(F5,2,','));

day=substr(worddate,1,2);

monthname=scan(worddate,2, ' ');

month=substr(monthname,1,3);

year=scan(worddate,3,' ');

date9=input(catt(day,month,year),date9.);

run;

proc print ; run;

Trusted Advisor
Posts: 1,050

Re: Excel-date format

Perfect. That's exactly the way to do things like this in SAS.

Your day assignment may not work with single digit dates.

day=substr(worddate,1,(notdigit(worddate)-1));

should fix it.

Tom

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 239 views
  • 0 likes
  • 2 in conversation