date 1111-11-11 in sas

[SujathaBalachandran]

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

[ballardw]

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.

[SujathaBalachandran]

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

[ballardw]

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.