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
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;
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;
Thanks Tom. That's what I was looking for. I didn't realize you could nest formats which is super useful.
thanks!
Rob
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!
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.
Thank you Tom!
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).
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
Any link/documentations on this usage of ??
Thanks,
Haikuo
Thanks, LinLin. I should have checked first.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.