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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.