have character string looks like a date but isn't - needs to be a date SAS ENTERPRISE

[maliksmom2000]

I have an excel spreadsheet that regradless of what I have done to the cell, I cannot get it to be a date - I only want it in SAS as a date - I don't care about the actual spreadsheet.  When I upload it it is a character string but I don't know how to make it be recognized as a date (maybe rearrange the data?).  Here what it says:

Each date in the character cell is in the following format: YYYY-MMM-DD

I have tried the input function but that didn't work 

I am using SAS Enterprise

[Reeza]

INPUT() is the correct method. Show what you've tried and we can tell you where you went wrong. 

---

@maliksmom2000 wrote:

I have an excel spreadsheet that regradless of what I have done to the cell, I cannot get it to be a date - I only want it in SAS as a date - I don't care about the actual spreadsheet.  When I upload it it is a character string but I don't know how to make it be recognized as a date (maybe rearrange the data?).  Here what it says:

Each date in the character cell is in the following format: YYYY-MMM-DD

I have tried the input function but that didn't work 

I am using SAS Enterprise

---

[VDD]

data have;
input mydate $10.;
cards;
2019-01-01
2012-11-02
;

data want;
set have;
wantdate = input(mydate, yymmdd10.);
format wantdate date9.;

run;

[maliksmom2000]

Ok Please don't laugh at me, I'm using SAS Enterprise for the advanced expressions so I am not sure exactly what to type in.  

The columns with the text that should be date format are:

Reporting

SenirorityDate

EffectiveDate

I put the following after playing for a minute to see if I could do it without sheepishly coming back to you but needless to say it was rejected.  So Sorry! This one is new for me.  

input( t1.SeniorityDate, DATE10. yymmdd10.)

[Astounding]

First, let's clear up what your data values actually look like.  You explain the format as YYYY-MMM-DD

Does that mean you are observing values like  2019-JAN-12  ??

I'm not sure that SAS has an informat to match.  As you suggest, you might have to rearrange the pieces:

newvar = input(scan(SeniorityDate, 3) || scan(SeniorityDate, 2) || scan(SeniorityDate, 1), date9.);

format newvar yymmdd10.;

[maliksmom2000]

Thank you so much for responding!  yes I am seeing (example) 1999-JAN-01

I believe I'd need to rearrange the text so it could be made into a date but without retyping it I don't really know how.  It's too many records in excel to play with.  

Does the newvar function do that?  I put the following into SAS Enterprise and got the following error:

PROC SQL NOEXEC;
SELECT (newvar = input(scan(t1.SeniorityDate, 3) || scan(t1.SeniorityDate, 2) || scan(t1.SeniorityDate, 1), date9.))
AS CALCULATION
FROM WORK.'FLD EMPLOYEES QUERY 01072019'n t1;
"ERROR: The following columns were not found in the contributing tables: newvar."

[maliksmom2000]

I tried using an INPUT function thinking I could change the characters to date but I couldn't.  If I changed the dates to remove the hyphen, is there an easier way to make them dates?  I can do that easily in excel.

19940101 (from 1994-JAN-01)

[art297]

@maliksmom2000 @Astounding,

If the the dates are yyyy-mmm-dd the ANYDTDTE11. informat appears to work. e.g.:

data have;
  input char_date $11.;
  date=input(char_date,anydtdte11.);
  format date date9.;
  cards;
2019-JAN-12
2019-JAN-13
;

Art, CEO, AnalystFinder.com

[maliksmom2000]

I will try it!

[maliksmom2000]

I cut and pasted what you have replacing char_date with my character dated column (SeniorityDate).  I played and changed it to:

input t1.SeniorityDate $11.;
date=input(t1.SeniorityDate,anydtdte11.);
format date date9.;
cards;
2019-JAN-12
2019-JAN-13
;

Which SAS didn't like either......

[art297]

You changed too much! The following modification of your code works for me:

data have;
  input SeniorityDate $11.;
  date=input(SeniorityDate,anydtdte11.);
  format date date9.;
cards;
2019-JAN-12
2019-JAN-13
;

+, after I suggested the above, I noticed in one of your other posts that you were trying to use proc sql. Here is a proc sql example for the same function:

data 'FLD EMPLOYEES QUERY 01072019'n;
  input x y z SeniorityDate $11.;
  cards;
1 2 3 2019-JAN-12
2 3 4 2019-JAN-13
;

PROC SQL;
  create table want as
    SELECT *, input(SeniorityDate, anydtdte11.) format=date9. as calculation
      FROM WORK.'FLD EMPLOYEES QUERY 01072019'n t1;
  ;
quit;

Art, CEO, AnalystFinder.com

[maliksmom2000]

I cut and pasted your exact text (sorry about playing with it, clearly I'm new to it 🙂 ).  Here's the error that I got:

4 PROC SQL NOEXEC;
5 SELECT (data have;
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||,
~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

6 input SeniorityDate $11.;
_____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

7 date=input(SeniorityDate,anydtdte11.);
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

8 format date date9.;
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
9 cards;
_____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

10 2019-JAN-12
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

11 2019-JAN-13
12 ;
12 ! ) AS CALCULATION
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

13 FROM WORK.'FLD EMPLOYEES QUERY 01072019'n t1;
14 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[maliksmom2000]

Then I replaced the SeniortyDate (by clicking on the table name) and removed the data have and got this:

4 PROC SQL NOEXEC;
5 SELECT (input t1.SeniorityDate $11.;
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

6 date=input(t1.SeniorityDate,anydtdte11.);
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

7 format date date9.;
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
8 cards;
_____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

9 2019-JAN-12
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

10 2019-JAN-13
11 ;
11 ! ) AS CALCULATION
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

12 FROM WORK.'FLD EMPLOYEES QUERY 01072019'n t1;
13 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[maliksmom2000]

I was able to do it, thank you!

[VDD · Accepted solution]

@maliksmom2000 you posted "I was able to do it, thank you!", 

That is great,

What solution provided you the answer or was the solution that you found something different?

Please close the tread by selection the solution that answered your request.