I have a query which pulls several columns from a master file, including a date field which may have null values, and then I pass that data to a hold table.

If I do not add a date format specification to the column when creating the hold table, the null values are preserved in the hold table. When printing the hold table you can verify the default null value of "." in the columns for certain rows.

However, if I add a date format specification such as \HYYMDI, (this is the actual format specified for the column in the master file), then the null values are NOT preserved when passing the data to the hold table. Null values no longer show up as '.' and you cannot use the MISSING keyword in where statements. Null values simply show as blank fields with no "."s. SET NODATA will also not work to rename the null date values because they cannot be found.

Can I anyone shed some light on this problem?

I have a larger, more complex procedure which passes data along several hold tables; needs to be able to evaluate null values while also specifying date formats on columns

Also, I have already tried the following commands: "SET ALL = ON", "SET COMPMISS=ON" SET MISSING = ON" SET HOLDMISS = ON" to no avail..

This message has been edited. Last edited by: Kerry,

Maybe I am not understanding the issue, but in the following code null values are preserved (using WF release 7.7.03):

SET HOLDLIST = PRINTONLY
-*
DEFINE FILE CAR
 SALES_DATE/HYYMDI MISSING ON = IF (COUNTRY NE 'FRANCE' OR 'JAPAN') THEN DT(&YYMD 00:00:00) ELSE MISSING ;
END
-*
TABLE FILE CAR
 PRINT
  COUNTRY
  CAR
  MODEL
  SALES
  SALES_DATE
 ON TABLE HOLD AS HOLDX
END
-*
TABLE FILE HOLDX
 PRINT
  COUNTRY
  CAR
  MODEL
  SALES
  SALES_DATE/HMDYYI
 ON TABLE HOLD AS HOLDY
END
-*
TABLE FILE HOLDY
 PRINT *
END

Dan, try running the following code

SET HOLDLIST = PRINTONLY
-*
DEFINE FILE CAR
SALES_DATE/HYYMDI MISSING ON = IF (COUNTRY NE 'FRANCE' OR 'JAPAN') THEN DT(&YYMD 00:00:00) ELSE MISSING ;
END
-*
TABLE FILE CAR

BY COUNTRY
BY CAR
BY MODEL
BY SALES
BY SALES_DATE/HMDYYI
ON TABLE HOLD AS HOLDX
END
-*
TABLE FILE HOLDX

BY COUNTRY
BY CAR
BY MODEL
BY SALES
BY SALES_DATE
ON TABLE HOLD AS HOLDY
END
-*
TABLE FILE HOLDY
PRINT *
WHERE SALES_DATE NE MISSING
END


Do you see how the nulls are not preserved?

I seem to remember that you cannot dynamically re-format fields used in BY phrases. Although an error message is not generated, this seems to be the problem. If you remove the re-format on SALES_DATE, the query works as expected:

SET HOLDLIST = PRINTONLY
-*
DEFINE FILE CAR
 SALES_DATE/HYYMDI MISSING ON = IF (COUNTRY NE 'FRANCE' OR 'JAPAN') THEN DT(&YYMD 00:00:00) ELSE MISSING ;
END
-*
TABLE FILE CAR
 BY COUNTRY
 BY CAR
 BY MODEL
 BY SALES
 BY SALES_DATE
 ON TABLE HOLD AS HOLDX
END
-*
TABLE FILE HOLDX
 BY COUNTRY
 BY CAR
 BY MODEL
 BY SALES
 BY SALES_DATE
 ON TABLE HOLD AS HOLDY
END
-*
TABLE FILE HOLDY
 PRINT *
 WHERE SALES_DATE NE MISSING ;
END

One solution is to re-format SALES_DATE correctly as a PRINT/SUM verb object:

SET HOLDLIST = PRINTONLY
-*
DEFINE FILE CAR
 SALES_DATE/HYYMDI MISSING ON = IF (COUNTRY NE 'FRANCE' OR 'JAPAN') THEN DT(&YYMD 00:00:00) ELSE MISSING ;
END
-*
TABLE FILE CAR
 BY COUNTRY
 BY CAR
 BY MODEL
 BY SALES
 BY SALES_DATE
 ON TABLE HOLD AS HOLDX
END
-*
TABLE FILE HOLDX
 SUM FST.SALES_DATE/HMDYYI
 BY COUNTRY
 BY CAR
 BY MODEL
 BY SALES
 ON TABLE HOLD AS HOLDY
END
-*
TABLE FILE HOLDY
 PRINT *
 WHERE SALES_DATE NE MISSING ;
END

Thank you for pointing that information out about dynamic reformatting and BY phrases. I will try converting all my queries to Print phrases.

However, one of the hurdles we face, is that we have a procedure that is grabbing data from 3 different sources and combining the data into one hold table. Since one of the 3 data sources has a different date format than the rest, we have to dynamically reformat it to 'HMDYYI' for the merge of all 3 hold tables to work. So I do not have the option of removing dynamic reformatting as a solution.

Instead of dynamically reformatting the date column, create a new column with DEFINE or COMPUTE, using the MISSING attribute previously suggested.

If sorting by the date is necessary, besides Francis' excellent DEFINE solution, you could also use PRINT/SUM to reformat the date and sort the date with a NOPRINT:

SET HOLDLIST = PRINTONLY
-*
DEFINE FILE CAR
 SALES_DATE/HYYMDI MISSING ON = IF (COUNTRY NE 'FRANCE' OR 'JAPAN') THEN DT(&YYMD 00:00:00) ELSE MISSING ;
END
-*
TABLE FILE CAR
 BY COUNTRY
 BY CAR
 BY MODEL
 BY SALES
 BY SALES_DATE
 ON TABLE HOLD AS HOLDX
END
-*
TABLE FILE HOLDX
 SUM FST.SALES_DATE/HMDYYI
 BY COUNTRY
 BY CAR
 BY MODEL
 BY SALES
 BY SALES_DATE NOPRINT
 ON TABLE HOLD AS HOLDY
END
-*
TABLE FILE HOLDY
 PRINT *
 WHERE SALES_DATE NE MISSING ;
END

I was able to get our procedure to work by re-defining the date column at the top and removing dynamic reformats when passing to the hold tables.

Thank you both very much for your input. It really helped.