Check for Zero in Input Statement

[rpmartin]

I'm importing some data that includes several columns that uses the YYMMDD8. informat. Most of the data is formatted correctly (eg 20090125), but some fields are just a series of zeros (00000000). I'd like a missing value for the records that have zeros.

I can make it work by importing it as a character variable and then using a statement like:

     if char_field ^= '00000000' then date_field = input(char_field, YYMMDD8.)

This seems clunky and is a bit tedious when there are several columns. Is there a way to use informats to check for a zero value? Or is there a better way to approach this?

thanks for your help,
Rob

[Tom · Accepted solution]

Use a custom informat.  You can nest the format so that you only need to define how to handle the special case.

proc format ;

  invalue zdate '00000000'=. other=[yymmdd8.] ;

run;

data test;

  input date zdate8.;

  format date yymmdd10.;

  put date= +1 _infile_;

cards;

00000000

20100608

run;

[rpmartin]

Thanks Tom. That's what I was looking for. I didn't realize you could nest formats which is super useful.

thanks!

Rob

[Linlin]

Hi Tom,

proc format ;

  invalue zdate '00000000'=. other=[yymmdd8.] ;

run;

when I changed "other=[yymmdd8.]" to "other=yymmdd8." it also worked, it did not work when I changed it to "other=(yymmdd8.) or other={yymmdd8.}". Do we have to use "[ ]"?

Thanks!

[Tom]

According to the SAS 9.2 manual you should use square brackets [ and ] or you can use two character sequence (| and |) instead.

But as you discovered they are not actually required. At least with 6.12, 8.2 and 9.2.

existing-informat

is an informat that is supplied by SAS or a user-defined informat. The informat you are creating uses the existing informat to convert the raw data that match value-or-range on the left side of the equal sign. If you use an existing informat, then enclose the informat name in square brackets (for example, [date9.]) or with parentheses and vertical bars, for example (|date9.|). Do not enclose the name of the existing informat in single quotation marks.

[Linlin]

Thank you Tom!

[ballardw]

Last time I worked with such imports reading text or csv files if the the data was out of range for a specified informat like this the result was missing. Year 0000 is valid but month and day 0 are both invalid and should result in missing. How are you importing the data? (HInt: wizards are pretty stupid about some values).

[Ksharp]

Why not use double question symbol to let sas do it for you.

data test;
  input date ?? yymmdd10.;
  format date yymmdd10.;
  put date= +1 _infile_;
cards;
00000000
20100608
;
run;

KSharp

[Haikuo]

Any link/documentations on this usage of ??

Thanks,

Haikuo

[Linlin]

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000180357.htm

[Haikuo]

Thanks, LinLin. I should have checked first.