07-27-2015 12:25 PM
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.
07-27-2015 12:42 PM
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:
infile cards dsd;
informat date mmddyy10.;
format date mmddyy10.;
format num_date 8.;
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;
Message was edited by: Mark Johnson
07-27-2015 12:43 PM
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.
07-27-2015 01:14 PM
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?
07-27-2015 04:52 PM
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= . ;
07-27-2015 05:17 PM
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?
07-27-2015 07:16 PM
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.