topic Re: Input statement retain file data as rows with the detail in SAS Programming

Input statement retain file data as rows with the detail

jimbobob — Wed, 17 Nov 2021 17:51:03 GMT

I have text files that are pipe delimited with no header, but has a header row that contains file date and some other fields followed by detail, followed by Trailer Record looks like this:

I want to retain the header record down thru the detail. I get close but instead of retaining the word HEADER throughout the data my first variable in my detail is getting split at 6 characters:

This is the logic I was using:

DATA WORK.SM_DB_NF_20211116;

	INFILE 'Y:\File Transfers\I2C\Incoming\SM_DB_NF_20211116.TXT'
 	LRECL=32767
 	FIRSTOBS=1
 	ENCODING="WLATIN1"
 	DLM='7c'x
 	MISSOVER
 	DSD;

	FORMAT

        F1               $CHAR6.
        F2               $CHAR3.
        F3               $CHAR13.
        F4               MMDDYY10.
        F5               MMDDYY10.
        F6               MMDDYY10.
        F7               BEST1. 
 	CARD_NUMBER	$CHAR19.
 	OPEN_DATE	DATE9.
 	EXPIRATION_DATE	MMDDYY10.
... more fields;

	RETAIN F1 F2 F3 F4 F5 F6 F7;
	input F1 $6. @;

	if F1 = "HEADER" then do;
	    INPUT
	        F1               : $CHAR6.
	        F2               : $CHAR3.
	        F3               : $CHAR13.
	        F4               : ?? MMDDYY8.
	        F5               : ?? MMDDYY8.
	        F6               : ?? MMDDYY8.
	        F7               : ?? BEST1. ;
	end;
	else do;
		INPUT
		 	CARD_NUMBER	: $CHAR19. 
		 	OPEN_DATE : ?? ANYDTDTE8.
		 	EXPIRATION_DATE	: ?? MMDDYY8.
		 	... more fields;
	end;
RUN;

Any help would be appreciated.

Re: Input statement retain file data as rows with the detail

Tom — Wed, 17 Nov 2021 19:30:28 GMT

To do something special on the first row just test the value of the automatic variable _N_.

Note: Use the LENGTH statement to DEFINE your variables. FORMAT is for attaching instructions for how to print the values. In your data it looks like you only need that for the variables that have DATE values. Normal numbers and character variables do not need to have any format attached to them.

DATA WORK.SM_DB_NF_20211116;
  INFILE 'Y:\File Transfers\I2C\Incoming\SM_DB_NF_20211116.TXT'
    ENCODING="WLATIN1"
    DSD DLM='|'
    TRUNCOVER
  ;

  LENGTH
      F1               $6
      F2               $3
      F3               $13
      F4               8
      F5               8
      F6               8
      F7               8
      CARD_NUMBER      $19
      OPEN_DATE        8
      EXPIRATION_DATE  8
... more fields
  ;

  FORMAT
      F4               MMDDYY10.
      F5               MMDDYY10.
      F6               MMDDYY10.
      OPEN_DATE DATE9.
      EXPIRATION_DATE MMDDYY10.
... more fields that REQUIRE formats attached ...
  ;

  INFORMAT
      F4               MMDDYY.
      F5               MMDDYY.
      F6               MMDDYY.
      OPEN_DATE        ANYDTDTE.
      EXPIRATION_DATE  MMDDYY.
... more fields that REQUIRE informats to be read correctly ...
  ;

  RETAIN F1 F2 F3 F4 F5 F6 F7;
  if _n_=1 then input F1-F7;
  INPUT
    CARD_NUMBER 
    OPEN_DATE ?? 
    EXPIRATION_DATE  ?? 
... more fields
  ;
RUN;

Re: Input statement retain file data as rows with the detail

jimbobob — Wed, 17 Nov 2021 19:58:20 GMT

Awesome thanks Tom

Re: Input statement retain file data as rows with the detail

Tom — Wed, 17 Nov 2021 20:05:20 GMT

You can also use the END= option on the INFILE statement to set a variable you can use to detect when you have reached the end of the file. To get it set before you try to read the line add and extra INPUT with trailing @.

...
  infile .... end=eof .... ;
...
  input @;
  if eof then do;
     put _infile_;
     if _infile_ ne: 'TRAILER' then put 'ERROR: Trailer record is missing.';
     else stop;     
  end;
  if _n_=1 then ...