Help using Base SAS procedures

formating date from a string to a number with a date format

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

formating date from a string to a number with a date format

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.


Accepted Solutions
Solution
‎02-04-2015 01:21 PM
Super User
Posts: 5,083

Re: formating date from a string to a number with a date format

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


All Replies
New Contributor
Posts: 2

Re: formating date from a string to a number with a date format

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 ; 
Solution
‎02-04-2015 01:21 PM
Super User
Posts: 5,083

Re: formating date from a string to a number with a date format

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.

Super Contributor
Posts: 398

Re: formating date from a string to a number with a date format

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.

Super User
Posts: 5,083

Re: formating date from a string to a number with a date format

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.


Super Contributor
Posts: 398

Re: formating date from a string to a number with a date format

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

Super User
Posts: 5,083

Re: formating date from a string to a number with a date format

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

input(dt, yymmdd10.)


Super Contributor
Posts: 398

Re: formating date from a string to a number with a date format

Thank you so much for all your help

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 570 views
  • 6 likes
  • 3 in conversation