BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JNWong
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

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

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
JNWong
Calcite | Level 5
Thanks,Miller.

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

Thank you!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5
Thanks,RW9.

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

Thank you!
Rwon
Obsidian | Level 7
data want;
set test;
format date2 yymmdd10.;
date2=input(date1,$10.);
run;
JNWong
Calcite | Level 5

Thanks,Rwon.

 

      i have tried your solution and it work out well. 

 

Thank you!

ErikLund_Jensen
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
Thanks,Ballardw.

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

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
Calcite | Level 5

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 !

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1908 views
  • 0 likes
  • 7 in conversation