topic Re: date 1111-11-11 in sas in SAS Data Management

date 1111-11-11 in sas

SujathaBalachandran — Mon, 27 Oct 2014 14:33:07 GMT

Hi,

I'm trying to generate an xl report from DB2 using SAS. Below is my code.

proc sql;

create mySasTbl as

select DateField --> This field can hold values 1111-11-11 or NULL or valid date

from db2table1

where cond1 = true;

run;

proc print data=mySasTbl label noobs;

run;

SAS treats the date 1111-11-11 as missing field and generates the report with dot(.). The report should get generated with any kind of value in DateField (1111-11-11 or NULL or valid date) as in DB2 table.

Any suggestion would be of great help.

Sujatha

Re: date 1111-11-11 in sas

ballardw — Mon, 27 Oct 2014 16:06:56 GMT

Is the variable DateField in mySasTbl supposed to be a SAS date variable? Generally SAS treats dates before 1582 as invalid as the Gregorian calendar (current) wasn't in general use before then.

And since you state "or valid date" then the value 1111-11-11 is some special value and isn't an actual date either.

I would be tempted to select ID values from the source data with that value and then update your mySasTbl with a special missing value so you can identify those records.

Re: date 1111-11-11 in sas

SujathaBalachandran — Tue, 28 Oct 2014 14:50:51 GMT

Thanks for the suggestion!

Null variables in DB2 and 1111-11-11 in db2 are converted to dot (.) by SAS. So I would need some measure in SAS to differentiate the NULL value in DB2 and 1111-11-11 in DB2, that I select from the DB2 table.

are there any methods to differentiate these two?

Sujatha

Re: date 1111-11-11 in sas

ballardw — Tue, 28 Oct 2014 14:55:20 GMT

Probably the easiest would be to have a pass through query in DB2 that creates a flag variable for the 1111-11-11 condition and then import the result of that query.