topic formating date from a string to a number with a date format in SAS Procedures

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

jerry898969 — Wed, 04 Feb 2015 17:36:41 GMT

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.

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

AirtonNunes — Wed, 04 Feb 2015 18:14:36 GMT

Try:

data temp ;
set temp_ ;
informat x MMDDYYD10.;
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) ;
x=catx('-',mon,day,yr) ;
output ;
end ;
DROP POS DT YR MON DAY;
run ;

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

Astounding — Wed, 04 Feb 2015 18:21:00 GMT

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.

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

jerry898969 — Wed, 04 Feb 2015 18:26:32 GMT

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.

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

Astounding — Wed, 04 Feb 2015 20:07:34 GMT

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.

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

jerry898969 — Wed, 04 Feb 2015 21:09:48 GMT

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

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

Astounding — Wed, 04 Feb 2015 21:49:41 GMT

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

input(dt, yymmdd10.)

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

jerry898969 — Thu, 05 Feb 2015 14:10:16 GMT

Thank you so much for all your help