Date column has inconsistencies across values

[kajal_30]

Hi Team,

I have a date column which is showing correct values in all the rows in a SAS dataset. But when I am loading the same dataset to Netezza database just few say 20 out of 200 values are showing date of 1/1/1960 rest column shows same dates as we have in the SAS dataset. we are using the same format of the column in SAS and Netezza. please advise 

Regards

kajal

[PaigeMiller]

Show us what you are seeing in SAS for some of these 20 rows that aren't showing properly, and also for a few rows that are showing properly. We need to see the unformatted values. You can un-format something simply by using this command:

format variablename;

Then show us the same rows as they appear in Netezza. 

Use the "Insert Photos" icons to include your screen captures in your reply.

[Tom]

So '01JAN1960'd is represented by the number zero in SAS.

Did you do something to convert missing values into zeros?

Also SAS stores dates as number of days since 1960 and datetime as number of seconds since 1960.  If you use DATE format with date values and DTDATE format with datetime values they will look in the output like the same thing, but actually contain wildly different values.   So if you accidentally tried to load a DATE value (number of days) into a variable that is using DATETIME values (number of seconds) it will appear as some time early in the first day of Jan 1960.

Run PROC CONTENTS on your SAS dataset and check the TYPE of the variables (numeric or character) and check what format (if any) is attached.

185  data test;
186    now=datetime();
187    today=date();
188    put 'As raw numbers -> ' (now today) (=comma20.);
189    put 'As DATE values -> ' (now today) (=date9.);
190    put 'As DATETIME    -> ' (now today) (=datetime19.);
191  run;

As raw numbers -> now=1,971,522,499 today=22,818
As DATE values -> now=********* today=22JUN2022
As DATETIME    -> now=22JUN2022:13:08:19 today=01JAN1960:06:20:18

[kajal_30]

Hi Tom the values showing 1/1/1960 are actually not null values in the source they have valid date value.

[kajal_30]

[PaigeMiller]

Did you un-format the SAS dates? It doesn't appear that you did this.

[kajal_30]

[PaigeMiller]

Thank you. Now show us the code you used to send this to Netezza.

[Reeza]

@kajal_30 to make the comparison easier. Create small sample of the data that has the issue, say 20 records. 

Show a screenshot of those twenty dates in SAS. 

Then show the screenshot in Netezza and include the code used as well. 

Screenshots of different records that don't necessarily align won't help resolve the issue.

This is called generating a reproducible example to illustrate a problem and is generally how you go about debugging any coding issues. About 80% of the time when going through this process you'll figure out where the error is before posting if you follow the process. 

https://stackoverflow.com/help/minimal-reproducible-example