converting sas date

[JNWong]

Hi,

 As i ran into a problem with sas date format. i have a dataset in which the varible "date1" is original sas date like"42308" "42307".

I think it may be a result substrated the "1960.."  .  However the format and informat for the variable is "$10." and it is a character varible.

 i need to get the result like"yymmdd10" which is "2016/11/13" ,for example.

i have tried the input function like "date2 = input(date1,yymmdd10.), but it did not work.

Thank you!

[PaigeMiller]

Assuming the values are actual integers and actual SAS dates, and not character string as you have typed them (e.g. "42308"), all you have to do is to apply the proper format to your variable. There is no conversion needed.

But 42308 is some time in the year 2075, so I don't think it is really a SAS date. So ...

MUCH MORE INFORMATION IS NEEDED ABOUT WHERE YOU GOT THIS DATA and anything else you can tell us about it. Really, please, start from the beginning, show us the code, show us the data, and then probably we can help you.

[JNWong]

Thanks,Miller.

i import the dataset from my excelfile. Jensen have put forward a solution,it seems work.

Thank you!

[RW9]

What date exactly does: 42308

Refer to?  I assume you have imported these from Excel?

"i have tried the input function like "date2 = input(date1,yymmdd10.), but it did not work." - Yes, this wont work, you can't read as that format as its not that format.  Try:

data want;
  set have;
  a=input(your_var,best.);
  format a date9.;
run;

[JNWong]

Thanks,RW9.

I would try your method latter and it may be useful for my further study.

Thank you!

[Rwon]

data want;
set test;
format date2 yymmdd10.;
date2=input(date1,$10.);
run;

[JNWong]

Thanks,Rwon.

      i have tried your solution and it work out well. 

Thank you!

[ErikLund_Jensen · Accepted solution]

Hi

Different systems have different epoch dates (reference date = value 0). Date 0 in SAS is 01jan1960 and 01jan1900 in eg. Excel.  As 42308 is the SAS date value for 01nov2075, it is possible that your date values might come from a system such as Excel, and in order to gen the correct SAS date, you must subtract the difference between  jan1960 and 01jan1900 from the value, which gives 02nov2015.

If the value is a string, you have to read it into a numeric value before the subtraction. Example:

data _null_;
   a = "42308";
   offset = '01jan1960'd - '01jan1900'd;
   b = input(a,5.) - offset;
   put b= date9.;

run;

b=02NOV2015

[JNWong]

Thanks,Jensen.

           As i have not found the problem yet, i just thought how to convert the format. I do import the dataset from my excelfile.when i change the format for 42308, it becomes 2075-11-01. a new problem arised.

          i think your method would solve my problem.

thank you!

[ballardw]

You don't mention HOW you brought the Excel data into SAS. My experience, especially if the Excel file is not generated by a system with clean data is to set the Excel columns that contain DATE values to the same Excel date display setting in Excel before bringing into SAS. Quite often then the value is much more likely to be the correct SAS date value and a useable format.

One of the truly obnoxious behaviors of Excel especially with manually entered data is that Excel will "guess" what you meant and create dates when it shouldn't, make your date represent a value other than you entered and mix character values and actual dates in ways that are not obvious within a single column.

[JNWong]

Thanks,Ballardw.

thanks for your suggestion, i should check the excelfile before importing to sas.

[Tom]

What has happened is that your column in Excel had mixed numeric (date) and character values. So it got imported into SAS as character and so the date format that was attached to those numbers was lost and you got the character version of the underlying number that Excel uses to store dates.  You first need to convert the string to a number and then adjust the number to account for differences between how Excel and SAS store dates.  You can then attach any of SAS's many date formats to have it display the way you want.

SAS uses 0 to mean 01JAN1960 and Excel uses 0 to mean 31DEC1899. But Excel also thinks that 1900 was a leap year so we need to subtract one more to adjust for that mistake.  If you really have dates before 01MAR1900 then you will need to make other adjustments for them.

data have ;
  input date1 $10.;
cards;
42308
42307
;

data want ;
  set have ;
  date = input(date1,32.) - '01JAN1960'd + '31DEC1899'd - 1;
  format date yymmdd10. ;
run;

Obs    date1          date

 1     42308    2015-10-31
 2     42307    2015-10-30

[JNWong]

Thanks,Tom

     I am so  confused that sas failed to identify the date format ,so i went back to my excelfile and found one observations of varible date is "2015-11-31" which my be an error made by the database. because of the error,the date imported as "text".

     as for the converting date between the two software, i have tried the code like"Excel_date = SAS_date + 21916." 

     i would try your code latter.

Thak you !