convert variable of Char and numeric to date

[jcurran1289]

Hi,

i have imported a DS from excel. some of the variables are made up of numeric values and char. I have attached it as it appears in excel. When i import it into SAS, it becomes a Char variable. I have no idea how to convert it in a SAS date so i can get the year of it etc. 

i used the following code in my data step but the out was simple "." for every value

var_form=input(var,yymmdd10.);

format var_form worddate.;

Can someone please help? i have a fewe variables like this and its driving me crazy. i can fix it in excel but need to do it in sas.

thanks

[ballardw · Accepted solution]

The problem is who ever created the Excel file didn't set what appears to be a date type for the entire column. If you are going to work with similar data in the future it should be addressed before importing into SAS to reduce problems.

Since the values are in at least two forms you will need multiple approaches to dealing with this.

First, the displayed values of your example are in mmddyy10. form not year first as yymmdd would imply.

The second is you need to adjust the Excel date value to match SAS. The are both numbers of days but the value bases are notably different.

If you have other date formats you'll need to modify this for those values.

Try this:

if index(var,'/') >0 then var_form=input(var, anydtdte.);

else var_form = input(var,best8.) +'30DEC1899'd;

[jcurran1289]

Thanks. My mistake before. thanks very much for the help!

[sh0e]

Try using MMDDYY10. as teh INFORMAT in the INPUT function.

In general, the INFORMAT must match the format of the data you wish to convert.

I suggest reviewing the details of the ANYDATE informat as well.

HTH - Jack