topic Re: convert variable of Char and numeric to date in SAS Programming

convert variable of Char and numeric to date

jcurran1289 — Tue, 12 Jul 2016 18:57:27 GMT

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

Re: convert variable of Char and numeric to date

ballardw — Tue, 12 Jul 2016 19:24:38 GMT

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;

Re: convert variable of Char and numeric to date

sh0e — Tue, 12 Jul 2016 19:30:22 GMT

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

Re: convert variable of Char and numeric to date

jcurran1289 — Wed, 20 Jul 2016 14:26:40 GMT

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