BookmarkSubscribeRSS Feed
SujathaBalachandran
Calcite | Level 5


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

3 REPLIES 3
ballardw
Super User

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
Calcite | Level 5

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1391 views
  • 3 likes
  • 2 in conversation