Hello,
I have a situation where I have a date value within a character field. Ex. 2015-01-20
I have substring that porition out of the text variable but I'm not sure how to convert it to a numeric field with the correct date format so I can select the rows that have a date larger then the 01/19/2015. In the code below I'm trying to take out the date parts and create a new variable and convert it to numeric but I keep getting "Invalid numeric data, dates='02-01-2015'" This is listed many times.
data temp ;
set temp_ ;
pos = find(text, '[2015') ;
if pos > 0 then do ;
dt = substr(substr(text, pos, 11),2) ;
yr=substr(dt,1,4) ;
mon=substr(dt,6,2) ;
day=substr(dt,9,2) ;
dates=catx('-',mon,day,yr) ;
x = input(datepart(dates), date11.) ;
output ;
end ;
run ;
Thank you for any help you can give me.
Jerry,
You're getting the message because X is a character variable. That means it can't be used as input to the DATEPART function. Looks like it doesn't contain a datetime value anyway, so ...
Simplifying your program should work:
data want;
set temp_;
pos = find(text, '[2015');
if pos > 0;
dt = substr(text, pos+1);
if input(dt, yymmdd10.) > '19jan2015'd;
** optionally, drop dt;
run;
Note that DT will contain some extra characters, but the INPUT function can be limited to read only the first 10.
Good luck.
Try:
Jerry,
You're getting the message because X is a character variable. That means it can't be used as input to the DATEPART function. Looks like it doesn't contain a datetime value anyway, so ...
Simplifying your program should work:
data want;
set temp_;
pos = find(text, '[2015');
if pos > 0;
dt = substr(text, pos+1);
if input(dt, yymmdd10.) > '19jan2015'd;
** optionally, drop dt;
run;
Note that DT will contain some extra characters, but the INPUT function can be limited to read only the first 10.
Good luck.
I just figured it out. I was using a format instead of an informat on my input statement. I did the following and it works. Do you see any problems with me doing it this way?
data temp |
set temp_ ;
pos = find(text, '[2015') ;
if pos > 0 then do ;
dt = substr(substr(text, pos, 11),2) ; | ||
dates = input(dt, anydtdte10.) ; | ||
if dates >= '01FEB2015'd then output ; |
end ;
run ;
Thank you so much for your help.
You're probably fine, but ...
Just to speed things up a hair, you might go with:
length dt $ 10;
dt = substr(text, pos+1);
ANYDTDTE should have no trouble with dates in YYMMDD10 form. If your incoming date values might take on other forms, you can expect that ANYDTDTE can read them but may not do so 100% correctly. For example, these incoming dates could force ANYDTDTE to make a choice:
01-02-2016 (which is the month and which is the day)
15-03-05 (15 could be a year or could be a day)
If you are happy letting the function decide, then you are fine. But if you expect that your dates will always be coming in as YYMMDD10., it would be safer to use that informat to let SAS flag any other form as invalid data.
Good luck.
Thank you so much for all your help.
The text date will always be yyyy-mm-dd. Should I take it in parts and create another variable in a date format?
Thank you
There's no need to break it up into pieces. This expression calculates the equivalent day on SAS's date scale:
input(dt, yymmdd10.)
Thank you so much for all your help
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.