how to convert char var to sas date?

[Reeza]

What do your dates look like:

01Jan2013 or 01/01/2013?

Post your full code as well.

[Cynthia_sas]

Hi:

  It is a bad idea of piggy-back onto an older post. Initially, when I saw the top post from 2012, I almost stopped reading.

  Here's the thing, you didn't show all your code. My guess is that your DATES array is referencing the original CHARACTER vars. Once those vars are declared as CHARACTER, you can't redefine them as numeric. So even though the INPUT function will turn a character string into a number given the right informat, you have to essentially make a new NUMERIC variable (or group of variables) to hold the new NUMERIC variables. My guess is that you might have defined both of your ARRAYs as character by accident. But since you did not show your ARRAY statements, it's hard to be sure.

cynthia

data testdate;

   length cd1 cd2 cd3 $10;

   array dates $ cd1-cd3; /* Character array */

   array newdates nd1-nd3; /* Numeric array */

   infile datalines;

   input cd1 $ cd2 $ cd3 $;

   do k=1 to dim(dates);

     newdates{k}=input(strip(dates{k}),mmddyy10.);

     put _n_= k= dates{k}= newdates{k}=date9.;

   end;

return;

datalines;

01/01/2011 01/15/1960 11/15/1950

06/18/1953 07/14/1956 11/29/1984

;

run;

ods listing;

proc contents data=testdate;

run;

[DebbiBJ]

Sorry Cynthia, I'm new to this community, so my etiquette is probably off...

I will post a thorough response on Monday; brain is too fried now.  I appreciate your willingess to help.  Have a lovely weekend!

[DebbiBJ]

Good morning!

I think the root of my problem is in the data, which I've PROC IMPORTed from an excel file.  I don't understand why, but the Import reads the same file differently on different runs.  That is, the subsequent PROC CONTENTS reads some of the date variables as character and some as numeric; the puzzling part is that it's not consistent which ones it calls which (when I run the program more than once).  The data came from someone else; I tried specifically formatting all the date columns as date fields (i've tried both generic and specific date formats), and then as character fields, but that did not solve the problem.  Do you have any thoughts?

[DebbiBJ]

OK, I've been plugging away at this all day.  My problem is that I am PROC IMPORTING an Excel file that has 15 or so date variables (columns).  Most of it works fine, but if a variable is is missing in the first record, then any subsequent values for that variable get Imported as a text field rather than a numeric or date field.  In the Data step, I've managed to convert them all to numeric fields.  But...  if the first value is missing then the values that *are* present are converting to weird dates - e.g., 14MAR2072 instead of 13MAR2012.  Haven't been able to figure out what to do about that.  Help?

[Tom]

Re-Post your question on a NEW thread. 

You can also search the site as this type of issue comes up frequently.

Trying to get Excel to behave as if it was a real database with defined variable types is your underlying problem.  PROC IMPORT has to guess at what type to make each variable based on what is sees in the current data file.  You would be better off storing the data as text files and reading them with a program.  Or switch to using a real database such as Access where the variable types will be available for PROC IMPORT or SAS/Access to use to define the SAS variables.

[DebbiBJ]

Thank you, I will try saving the Excel file as text.  The data don't originate with me, so I don't have much control over how they arrive.

[DebbiBJ]

Hello again,

I'm sorry, I don't know how to "re-post this as a new thread."

But, saving the Excel file as text worked almost perfectly.  PROC IMPORT read all but 3 of the 58 date variables as numeric.  I used Cynthia's logic to convert those three.

I did, however, encounter another problem.  I think I know how to start a new thread, but I don't know how to "re-post."

Thanks!

[NewLearner]

Hi,

I have similar kind of question, i am trying to compare two date columns (from two diffrent tables).

Table1's date column 

SOURCE_TIMESTAMP char(26) format=$CHAR26. informat=$CHAR26.,

Table 2's date column is

SOURCE_TIMESTAMP num format=DATETIME26.6 informat=DATETIME26.6 label='SOURCE_TIMESTAMP',

how to convert Table1's column to DATETIME datatype, so that i can compare between these two tables ?

Thanks for your help

[ballardw]

It's better on this forum to start a new question instead of piggybacking on one that is over a year old.

Also providing some example data, especially when character, is a good idea.

But

New_timestamp = input(source_timestamp,datetime26.6); would add a variable in a  data step if the two are basically the same range of values.

Or in Proc SQL

input(source_timestamp,datetime26.6) as New_timestamp

You will also want to assign an appropriate format to New_timestamp to see the values in more human readable form.

If the above datetime informat doesn't work you may want to try ANYDTDTM16.

[Mit]

Try this code... works for me when I have a character date.

data have;

set have;

day=substr(date,1,2);

month=substr(date,4,2);

year=substr(date,7,4);

run;

data have;

set have;

day1=day*1;

month1=month*1;

year1=year*1;

drop day year month;

run;

data want;

set have;

Date1=input(catx('/',day1,month1,year1),ddmmyy10.);

format Date1 ddmmyy10.;

drop date;

run;

[Community_Help]

Hi Everyone - This particular thread has the been the #1 page in communities for Google Traffic for the past year -- and probably before that.

It would be great if we mark any of the answers correct or helpful. As Admin, I can do this for you.. Just let me know what is correct or helpful. (We can have only 2 helpful answers per thread).  Or, ZRick if you happen to get this message and want to mark it - that would be great.

cc to and

[art297]

Lainie: This thread actually was used by at least four different people to ask related questions. We never discovered whether the original poster ever had his question answered or what was causing the problem.

[ChrisHemedinger]

I have "locked" this discussion and awarded some Helpful answers that addressed at least two of the specific questions.  If you're reading this and find that it doesn't answer your specific query, create a new topic with your own example data and what you're trying to achieve.  SAS can transform character data to any numeric (date, datetime, and so on) and vice versa. 

This blog also contains some helpful advice for using PUT and INPUT to convert values.

[ChrisHemedinger]

I have "locked" this discussion and awarded some Helpful answers that addressed at least two of the specific questions.  If you're reading this and find that it doesn't answer your specific query, create a new topic with your own example data and what you're trying to achieve.  SAS can transform character data to any numeric (date, datetime, and so on) and vice versa. 

This blog also contains some helpful advice for using PUT and INPUT to convert values.