topic Re: Please help - converting text date to date in SAS Procedures

Please help - converting text date to date

deleted_user — Wed, 28 Oct 2009 19:55:25 GMT

I have a list of dates stored in an Excel file as text. They are stored as 9/1/2001, 9/2/2001, etc. But their type are all texts. When read these values into SAS using import they are stored as characters. I tried everything trying to convert them to date format but just couldn't work. Anyone know how to get around this? Thanks!

Re: Please help - converting text date to date

Bill — Wed, 28 Oct 2009 20:17:13 GMT

perhaps this will work for you ...

data sasfile;
set xlfile;
sasdate=input(xldate,ddmmyy10.);
run;

Re: Please help - converting text date to date

deleted_user — Wed, 28 Oct 2009 20:22:58 GMT

The original format of the data is in mmddyy format. If I use ddmmyy10. will that mess up the mm and dd?

Re: Please help - converting text date to date

Bill — Wed, 28 Oct 2009 20:28:38 GMT

use

data sasfile;
set xlfile;
sasdate=input(xldate,mmddyy10.);
run;

Re: Please help - converting text date to date

Cynthia_sas — Wed, 28 Oct 2009 20:45:22 GMT

Hi:
Is 9 the MONTH or the DAY???? September 1 or 9 January??? If 9 is the MONTH, then the INFORMAT for your function call (to convert from a character date to a numeric date) would be the MMDDYY informat.

If the 9 is the DAY and 1 is the MONTH then you would use a different INFORMAT, as shown in the other answer.

The explanation for WHY you need to convert is that SAS dates are represented as the number of days from a 0 date. SAS uses Jan 1, 1960 as the 0 date. So, for example, compare these calendar dates with their internally stored values.
[pre]
calendar date: | Nov 15, 1950 | Dec 31, 1959 | Jan 1, 1960 | Jan 2, 1960 | Nov 15, 1960
SAS date value: | -3334 | -1 | 0 | 1 | 319
[/pre]

When represented on a scale line this, SAS date values can be negative numbers and represent time all the way back to the start of the Gregorian calendar, as described here:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001397898.htm
...where it explains that:
" Note: SAS date values are valid for dates based on the Gregorian calendar from A.D. 1582 through A.D. 19,900. Use caution when working with historical dates. Although the Gregorian calendar was used throughout most of Europe from 1582, Great Britain and the American colonies did not adopt the calendar until 1752."

So when you have a text string that represents a date value, if you want to work with that variable, as a DATE value, in a SAS program or so you can use SAS formats or calculate time intervals, you have to convert the character string to a number using an INPUT function. When you convert a character variable or constant to a numeric variable, you need an INFORMAT that is appropriate for the conversion. In this case you would use an INFORMAT that would take your character string and convert it to the number of days since Jan 1, 1960.

Here are some more documentation links about working with SAS date values:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001397930.htm
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001397901.htm
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001397947.htm

cynthia

Re: Please help - converting text date to date

deleted_user — Wed, 28 Oct 2009 21:09:30 GMT

I used the exact same step and I get the following:

NOTE: Invalid argument to function INPUT at line 12 column 16.
a=10/10/2002 b=23APR2009 c=10/10/2002 datebook=. _ERROR_=1 _N_=93
NOTE: Invalid argument to function INPUT at line 12 column 16.
a=10/10/2002 b=22APR2009 c=10/10/2002 datebook=. _ERROR_=1 _N_=94
NOTE: Invalid argument to function INPUT at line 12 column 16.
a=10/10/2002 b=20APR2009 c=10/10/2002 datebook=. _ERROR_=1 _N_=95
NOTE: Invalid argument to function INPUT at line 12 column 16.
a=10/10/2002 b=20APR2009 c=10/10/2002 datebook=. _ERROR_=1 _N_=96
NOTE: Invalid argument to function INPUT at line 12 column 16.

Re: Please help - converting text date to date

deleted_user — Wed, 28 Oct 2009 21:12:20 GMT

9 data try;
10 set book;
11
12 datebook = input(a, mmddyy10.);
13 run

Re: Please help - converting text date to date

deleted_user — Wed, 28 Oct 2009 21:20:18 GMT

The data are in month/day/year format. They are stored in Excel but in text format. When I use import to import the data using SAS, the variable ended up in character type. I am trying to convert it to numeric type with date format. This should be a very short program. However, I keep getting errors. Here's my code:

data try;
set imported_data;

new_date_var = input(old_char_var, mmddyy10.);
run;

I changed the variable names to make it clear. I get the same error messages in the log window for Bill. I am not sure what I am doing wrong. You said I should use an informat for this input function. I tried it also and it still doesn't work.

I tried both of the following code:
data try;
set imported_data;

new_date_var = input(old_char_var, mmddyy10.);
informat new_date_var date9.;
run;

data try;
set imported_data;

informat new_date_var date9.;
new_date_var = input(old_char_var, mmddyy10.);
run;

thanks again for your help.

Thanks for your help too, Bill!

Re: Please help - converting text date to date

Cynthia_sas — Wed, 28 Oct 2009 22:16:09 GMT

Hi:
Try this:
[pre]
proc contents data=imported_data;
title 'PROC CONTENTS';
run;

proc print data=imported_data;
title 'Without any formats';
format _numeric_ _character_; <-- or use whatever your variable names are;
run;
[/pre]

The NULL format statement removes any usage of SAS formats from the display of the data -- so you would see the numbers and text strings without any SAS format being used. So, if you still see 10/1/2002 in the PROC PRINT output, then you know the variable is CHARACTER. The PROC CONTENTS will also tell you whether the variable is character or numeric and whether any FORMATS or INFORMATS have been applied to the variable.

You might switch to the ANYDTDTE INFORMAT in your INPUT function, if MMDDYY10 doesn't work.
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a002605538.htm

Is it possible your variable is a DATE/TIME variable in Excel and NOT just a DATE variable???? When you go into Excel and look at the format for that column, is the Excel format set explicitly to TEXT or is the Excel format a DATE or DATE/TIME format????

cynthia

Re: Please help - converting text date to date

deleted_user — Thu, 29 Oct 2009 14:38:02 GMT

Hi Cynthia,

The variable is Character. I confirmed it by running the code you have shown me. Except of using variable names directly, I used _all_. Because when I use a specific variable name it tells me that variable is not found. For example, if I used

format _variable1_;

It will tell me variable1 not found in data imported_data. That's why I used

format _all_;

instead.

Now that I know the variable is character, how do I convert it to date? I have tried the code earlier from Bill and you and they didn't work. The original format in Excel is set explicitly to Text. I am so stuck right now.

Re: Please help - converting text date to date

sbb — Thu, 29 Oct 2009 15:01:53 GMT

The SAS support http://support.sas.com/ website has SAS-hosted documentation and supplemental technical and conference reference material.

Scott Barry
SBBWorks, Inc.

Recommended reading:

SAS Language Reference: Concepts - About SAS Date, Time, and Datetime Values
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002200738.htm

A Beginners Guide to SAS  Date and Time Handling
Wayne Finley, State of California HHSDC, Sacramento, CA

http://www2.sas.com/proceedings/sugi25/25/btu/25p058.pdf

SAS Constants in Expressions - DATE and TIME references discussed here:
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a000780334.htm

Re: Please help - converting text date to date

deleted_user — Thu, 29 Oct 2009 15:02:36 GMT

This is too weird. I just run the code again and everything worked. The code I used is what I have used yesterday.

data try;
set imported_data;

new_date_var = input(old_char_var, mmddyy10.);
informat new_date_var mmddyy10.;
format new_date_var mmddyy10.;
run;

However, when I run this yesterday I get the error messages that I have posted above. When I run them again today everything worked fine. Why is that?

Re: Please help - converting text date to date

sbb — Thu, 29 Oct 2009 15:20:48 GMT

Without the SAS logs and more details about your processing environment differences/changes from day-to-day, it's a crap-shoot to figure out -- unless yours was a rhetorical question, asked to the digital fjord.

Scott Barry
SBBWorks, Inc.

Re: Please help - converting text date to date

deleted_user — Thu, 29 Oct 2009 15:24:02 GMT

Actually, that didn't work. It worked on 90% of the data. 10% of the data ended up with missing values. Why is this?

Re: Please help - converting text date to date

deleted_user — Thu, 29 Oct 2009 15:35:36 GMT

Here is a sample output:

Obs char_var date_var
90 10/9/2002 10/09/2002
91 10/9/2002 10/09/2002
92 10/9/2002 10/09/2002
93 10/10/2002 .
94 10/10/2002 .
126 10/30/2002 .
127 10/31/2002 .
128 11/1/2002 11/01/2002
129 11/2/2002 11/02/2002

The . did not line up right. But they represent missing values. I do not understand this. If they are reading in the same variable how come they can read in only partial of it?

Message was edited by: cosmid

Message was edited by: cosmid

Message was edited by: cosmid Message was edited by: cosmid

Re: Please help - converting text date to date

deleted_user — Thu, 29 Oct 2009 15:40:05 GMT

It isn't a rhetorical question. I have copy and pasted the logs above. I am trying to read in a variable listing dates but in text format from Excel to SAS. The variable ended up in character and some ended up missing.

Re: Please help - converting text date to date

deleted_user — Thu, 29 Oct 2009 15:50:18 GMT

I think I know why some of the values ended up missing. Because of the default length of the variable and the data weren't read completely and that's why they ended up in missing.

how do i delete posts that i have posted?

Re: Please help - converting text date to date

sbb — Thu, 29 Oct 2009 17:35:33 GMT

Your best opportunity is to add the following SAS statement to your DATA step (after all assignment statements) for diagnostic and self-initiated desk-checking:

PUTLOG _ALL_;

Also, sharing the SAS generated log with your DATA step code into a post-reply will get the most distance from others' helping debug your program and input data.

Scott Barry
SBBWorks, Inc.

Re: Please help - converting text date to date

Bill — Thu, 29 Oct 2009 18:50:01 GMT

cosmid;

Might some of the dates be invalid thus generating a missing value, e.g. month value > 12, day value >28/29/30/31 in their respective months? SAS does that error checking. I'd be surprised if xl did.

Re: Please help - converting text date to date

deleted_user — Fri, 30 Oct 2009 20:49:32 GMT

Cosmid,

Did you do the import in SAS/EG?
Or, are you using PC SAS?
Or, are you running regular batch SAS?

It makes a difference in what the product "remembers" with reruns of stuff.

It is possible that you need to use numerical_date = input(trim(left(text_date)),mmddyy10.);

You should be able to define in the proc import the informat for the column in the excel file you are importing.

If you are using SAS/EG, you may need to delete the old import and create a new one to be able to get the filed lengths and informats set properly and remembered properly. Import in SAS/EG is a bit flaky at times.