BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rpmartin
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Tom
Super User Tom
Super User

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

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

thanks!

Rob

Linlin
Lapis Lazuli | Level 10

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

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
Lapis Lazuli | Level 10

Thank you Tom!

ballardw
Super User

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

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
Onyx | Level 15

Any link/documentations on this usage of ??

Thanks,

Haikuo

Haikuo
Onyx | Level 15

Thanks, LinLin. I should have checked first.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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