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
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;
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;
Thanks. My mistake before. thanks very much for the help!
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.