topic Re: Check for Zero in Input Statement in SAS Programming

Check for Zero in Input Statement

rpmartin — Mon, 24 Sep 2012 16:59:46 GMT

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

Re: Check for Zero in Input Statement

Tom — Mon, 24 Sep 2012 17:38:05 GMT

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;

Re: Check for Zero in Input Statement

ballardw — Mon, 24 Sep 2012 17:44:51 GMT

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).

Re: Check for Zero in Input Statement

rpmartin — Mon, 24 Sep 2012 18:15:25 GMT

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

thanks!

Rob

Re: Check for Zero in Input Statement

Linlin — Mon, 24 Sep 2012 20:55:08 GMT

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!

Re: Check for Zero in Input Statement

Tom — Mon, 24 Sep 2012 21:18:16 GMT

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.

Re: Check for Zero in Input Statement

Linlin — Mon, 24 Sep 2012 22:17:10 GMT

Thank you Tom!

Re: Check for Zero in Input Statement

Ksharp — Tue, 25 Sep 2012 04:30:09 GMT

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

Re: Check for Zero in Input Statement

Haikuo — Tue, 25 Sep 2012 13:52:24 GMT

Any link/documentations on this usage of ??

Thanks,

Haikuo

Re: Check for Zero in Input Statement

Linlin — Tue, 25 Sep 2012 14:06:18 GMT

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

Re: Check for Zero in Input Statement

Haikuo — Tue, 25 Sep 2012 14:11:09 GMT

Thanks, LinLin. I should have checked first.