topic Re: Date column has inconsistencies across values in SAS Programming

Date column has inconsistencies across values

kajal_30 — Wed, 22 Jun 2022 16:48:39 GMT

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

Re: Date column has inconsistencies across values

PaigeMiller — Wed, 22 Jun 2022 16:56:59 GMT

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.

Re: Date column has inconsistencies across values

Tom — Wed, 22 Jun 2022 17:10:00 GMT

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

Re: Date column has inconsistencies across values

kajal_30 — Wed, 22 Jun 2022 17:24:59 GMT

Re: Date column has inconsistencies across values

kajal_30 — Wed, 22 Jun 2022 17:26:22 GMT

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

Re: Date column has inconsistencies across values

PaigeMiller — Wed, 22 Jun 2022 17:29:01 GMT

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

Re: Date column has inconsistencies across values

kajal_30 — Wed, 22 Jun 2022 17:54:50 GMT

Re: Date column has inconsistencies across values

PaigeMiller — Wed, 22 Jun 2022 17:56:26 GMT

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

Re: Date column has inconsistencies across values

Reeza — Wed, 22 Jun 2022 18:37:53 GMT

@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