BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
26 REPLIES 26
Bill
Quartz | Level 8
perhaps this will work for you ...

data sasfile;
set xlfile;
sasdate=input(xldate,ddmmyy10.);
run;
deleted_user
Not applicable
The original format of the data is in mmddyy format. If I use ddmmyy10. will that mess up the mm and dd?
Bill
Quartz | Level 8
use

data sasfile;
set xlfile;
sasdate=input(xldate,mmddyy10.);
run;
deleted_user
Not applicable
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.
deleted_user
Not applicable
9 data try;
10 set book;
11
12 datebook = input(a, mmddyy10.);
13 run
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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!
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
deleted_user
Not applicable
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?
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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.
deleted_user
Not applicable
Actually, that didn't work. It worked on 90% of the data. 10% of the data ended up with missing values. Why is this?

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
  • 26 replies
  • 2733 views
  • 0 likes
  • 4 in conversation