BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

7 REPLIES 7
AirtonNunes
Calcite | Level 5

Try:

  1. data temp ;     
  2.     set temp_ ; 
  3.    informat x MMDDYYD10.;
  4.     pos = find(text, '[2015') ; 
  5.     if pos > 0 then do ; 
  6.         dt = substr(substr(text, pos, 11),2) ;     
  7.         yr=substr(dt,1,4) ; 
  8.         mon=substr(dt,6,2) ; 
  9.         day=substr(dt,9,2) ; 
  10.         x=catx('-',mon,day,yr) ;       
  11.         output ; 
  12.     end ;      
  13.    DROP POS DT YR MON DAY;
  14. run ; 
Astounding
PROC Star

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.

jerry898969
Pyrite | Level 9

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.

Astounding
PROC Star

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.


jerry898969
Pyrite | Level 9

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

Astounding
PROC Star

There's no need to break it up into pieces.  This expression calculates the equivalent day on SAS's date scale:

input(dt, yymmdd10.)


jerry898969
Pyrite | Level 9

Thank you so much for all your help

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!

What is Bayesian Analysis?

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.

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