DATA Step, Macro, Functions and more

convert variable of Char and numeric to date

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

convert variable of Char and numeric to date

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


sas_c.PNG

Accepted Solutions
Solution
‎07-20-2016 10:26 AM
Super User
Posts: 11,343

Re: convert variable of Char and numeric to date

Posted in reply to jcurran1289

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;

View solution in original post


All Replies
Solution
‎07-20-2016 10:26 AM
Super User
Posts: 11,343

Re: convert variable of Char and numeric to date

Posted in reply to jcurran1289

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;

New Contributor
Posts: 4

Re: convert variable of Char and numeric to date

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

Contributor
Posts: 25

Re: convert variable of Char and numeric to date

Posted in reply to jcurran1289

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 278 views
  • 0 likes
  • 3 in conversation