Learning SAS? Welcome to the exclusive online community for all SAS learners.

Unable to assign 0 where date value is 1/1/1900 in excel

Reply
Occasional Contributor
Posts: 17

Unable to assign 0 where date value is 1/1/1900 in excel

Hi Experts,

I am importing an excel file wherein Date column has some dates like 1/1/1900. While exporting the dataset, I want to assign 0 to all the dates that have year 1900. I tried using year function in the if condition and then assign 0 to the date but when I export the dataset, the value shown in the date column is 1/1/1960. Please advice as to how should I implement this logic.

Thank you so much for helping.

Regards !!

Gurpreet Kaur

Valued Guide
Posts: 858

Re: Unable to assign 0 where date value is 1/1/1900 in excel

If you have  date field = to zero and it's formatted like you show it here as mmddyy10. that date will show 01/01/1960.  If you want to show a zero in some fields and a real date in this format in others you will have to change it to a character field which eliminates any ability to do calculations based on dates.  Or you can remove the date format and have it as a number, in that case 01/01/2015 will equal 20089 which is how many days have passed since 01/01/1960.  If you run this code below pay attention to the tops of the columns where it shows you the formatting for each. 

Hope this helps:

data have;

infile cards dsd;

informat date mmddyy10.;

format date mmddyy10.;

input date;

cards;

01/01/1900

01/01/2015

;

data want;

format num_date 8.;

set have;

char_date = input(put(date,mmddyy10.),$char10.);

if char_date = '01/01/1900' then char_date = '0';

num_date = date;

if date = '01JAN1900'd then num_date = 0;

run;

Message was edited by: Mark Johnson

Super User
Posts: 17,868

Re: Unable to assign 0 where date value is 1/1/1900 in excel

I don't have an answer to your question, but can explain why it happens.

SAS and Excel store dates as numbers, so 0 is January 1, 1960 in SAS, but Excel uses a different starting date.

Given the system of storing data as numbers, setting the value to 0 may not be an ideal solution depending on what you're trying to represent. 

Super User
Posts: 10,516

Re: Unable to assign 0 where date value is 1/1/1900 in excel

If you are formatting the date field in Excel as a date then the value of 0 will display as the equivalent of 1/0/1900, i.e. day 0 of 1900. So I suspect you aren't going to get a SAS date value to be interpreted correctly for that purpose. You may have to change how you are exporting, i.e. create a CSV file with PUT statements to allow conditionally changing individual values OR chance the value you want to export to Excel.

What were you planning on doing with that value of 0 in Excel?

Super User
Super User
Posts: 6,502

Re: Unable to assign 0 where date value is 1/1/1900 in excel

Sounds like you did exactly what you said you wanted.  The date '01JAN1960'D has the value 0 when displayed as a regular number instead of a date.  So if you set the value to zero it will be displayed as 01JAN1960 when using DATE9. format.

You would be better served to convert the value to MISSING instead of zero.

if year(datevar) = 1900 then datevar= . ;

Valued Guide
Posts: 2,175

Re: Unable to assign 0 where date value is 1/1/1900 in excel

just like excel allows users to create custom formats that present negative numbers and zero differently from positive numbers, SAS allows us to present values (including dates and strings) with different formats for values in different ranges.

With this facility (user formats created by proc format) SAS could present your zlero dates any way you wish. More easily than in excel, all 1900 dates can have special formatting with a SAS user format.

Does this provide what is wanted?

Respected Advisor
Posts: 3,896

Re: Unable to assign 0 where date value is 1/1/1900 in excel

Dates are stored internally as numbers and a value of zero represents a date as well. Better convert "wrong" dates to missing and then apply a format in Excel which shows missing values as zero.

Ask a Question
Discussion stats
  • 6 replies
  • 970 views
  • 0 likes
  • 7 in conversation