DATA Step, Macro, Functions and more

Check for Zero in Input Statement

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Check for Zero in Input Statement

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


Accepted Solutions
Solution
‎09-24-2012 01:38 PM
Super User
Super User
Posts: 6,502

Re: Check for Zero in Input Statement

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;

View solution in original post


All Replies
Solution
‎09-24-2012 01:38 PM
Super User
Super User
Posts: 6,502

Re: Check for Zero in Input Statement

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;

New Contributor
Posts: 4

Re: Check for Zero in Input Statement

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

thanks!

Rob

Super Contributor
Posts: 1,636

Re: Check for Zero in Input Statement

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!

Super User
Super User
Posts: 6,502

Re: Check for Zero in Input Statement

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.

Super Contributor
Posts: 1,636

Re: Check for Zero in Input Statement

Thank you Tom!

Super User
Posts: 10,538

Re: Check for Zero in Input Statement

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

Super User
Posts: 9,688

Re: Check for Zero in Input Statement

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

Respected Advisor
Posts: 3,124

Re: Check for Zero in Input Statement

Any link/documentations on this usage of ??

Thanks,

Haikuo

Super Contributor
Posts: 1,636

Re: Check for Zero in Input Statement

Respected Advisor
Posts: 3,124

Re: Check for Zero in Input Statement

Thanks, LinLin. I should have checked first.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 447 views
  • 2 likes
  • 6 in conversation