How to Read in Invalid Date Values in Records

[ks01]

Hello ...

I am trying to read a raw data file. Here is a sample of my data:

[tt]240010002799XXXX8.%0910090928241910000000000000000704030318507040303185231755
240010300000XXXX8.%9999999999943410000000000000001208070734212080707342165858
240010300000XXXX6.%99999999999054 1021080829410210808294010911
240010300000XXXX9.%9999999999904410000000000000001026070729910260707299174528[/tt]

Here is the code that I’m working on:

OPTIONS OBS=100;                                     
DATA R001;                                           
INFILE INPUT1;                                       
  INPUT @01 RECTYP $CHAR1. @;                        
    IF RECTYP = '2' THEN                             
       DO;                                           
         INPUT @002 ACCOUNT                  $CHAR16.
               @018 ACCT_LENG_VAL            $CHAR2. 
               @020 PURGE_DATE_YYMMDD        YYMMDD6.
               @026 PURGE_DATE_YYDDD         JULIAN5. 
               @031 ACTION_CODE              $CHAR2. 
               @033 CARD_TYPE                $CHAR1. 
               @034 REGION_FLAGS             $CHAR16.
               @050 ORIG_NON_APPR_MMDDYY     MMDDYY6.
               @056 ORIG_NON_APPR_YYDDD      JULIAN5. 
               @061 EFF_DATE_MMDDYY          MMDDYY6.
               @067 EFF_DATE_YYDDD           $CHAR5.

My problem is with the PURGE_DATE_YYDDD AND ORIG_NON-APPR_YYDDD variables.

My problem is, the application that produces this data allows the user to enter a non-expiring purge date. It records this date value with all nines. There may be a need to perform calculations on the date, so I want to read the data in as a date, but SAS is hiccupping and the SAS log indicates “INVALID DATA” for each record.

Does anyone have any suggestions? I am a novice when it comes to SAS.

Our shop runs version 9.1.3 SP4 on z/OS.

Thank you!

 

[ChrisW75]

I've hit this many times now. There are 2 options.
1 - Just let the code run, it'll generate a bunch of errors, but WILL actually work, and you'll end up with missing values for those bogus dates.
2 - My preferred method is to read the data in as a character field, then test the field to see if it's valid (ie make sure it's not '999999') and if it is, convert it to a date. Like this:-

  if PURGE_DATE_YYDDD_TX ne '999999' then
    PURGE_DATE_YYDDD = input(PURGE_DATE_YYDDD_TX,yymmdd6.);

That should do the trick for you.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[ks01]

Hi Chris ...

As always, thanks for the guidance!

Is there a way to set the value of the purge date equal to, say like 9999-12-31 (assuming that is a valid date is SAS) for any record who has an input value of 99999?

Thanks again ...

Kent

 

[ChrisW75]

yup, change the code to

  if PURGE_DATE_YYDDD_TX ne '999999' then
    PURGE_DATE_YYDDD = input(PURGE_DATE_YYDDD_TX,yymmdd6.);
  else
    PURGE_DATE_YYDDD = '31DEC9999'd;

I generally use 2650 as the year though, however this is just a hangup from working on Oracle, for which the maximum allowed date is 31/12/2650. I'm not actually sure if 9999 is a valid year for a SAS date, give it a try and see I guess.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[mdieckman]

I'm pretty sure 12-31-9999 is a valid sas date as that is the default value for an unknown date where I work. I've never encountered an error with the value.