Reading COPYBOOK external file through BASE SAS Code

[Abhi1212]

Super.. I understood now.. I am new SAS your explanation made me very clear. Thanks for patient reply 🙂

Meanwhile, how u r defining the below? I mean how x+4 and X-1?

substr(a,x+4);

substr(a,1,x-1)

[Kurt_Bremser]

---

@Abhi1212 wrote:

Super.. I understood now.. I am new SAS your explanation made me very clear. Thanks for patient reply 🙂

Meanwhile, how u r defining the below? I mean how x+4 and X-1?

substr(a,x+4);

substr(a,1,x-1)

---

Since x denotes the position of the "A" of "ACMT", I need to subtract 1 when doing the substr, or the "A" would be part of the result.

Similarly, I need to add 4 so that the "ACMT" is discarded when I cut off the line that was just output.

[Abhi1212]

One more problem Sir.. Since ACMT is not available in last line of record.. The last line got stored in buffer. It is not coming in output. Once this is resolved...I will acheive my required output. Please help

Thanks

Abhi

[Kurt_Bremser]

Found the reason for the last line missing.

During the read loop:

do while (x = 0);
  input a1 $100.;
  a = trim(a) !! a1;
  x = index(a,'ACMT');
end;

when reading the last portion of the file, since no ACMT is found, the loop tries to read once more after the EOF is already reached. This causes a LOST CARD message in the log, and the whole data step is terminated without executing the part that should make sure that the last "line" is written to the output (the if done block).

So we also have to check the EOF condition in the read loop:

do while (x = 0 and not done);
  input a1 $100.;
  a = trim(a) !! a1;
  x = index(a,'ACMT');
end;

Now you also get the final line, as intended.

[Abhi1212]

Hello Sir,

How to make this code in macro to automate the process? 

[Kurt_Bremser]

---

@Abhi1212 wrote:

Hello Sir,

How to make this code in macro to automate the process? 

---

Help in automating:

First, lets simplify the length parameters:

%let biglen=2000;
%let buflen=100;

data new;
infile "$HOME/FI2016021301394.dat" dsd truncover recfm=f lrecl=&buflen end=done;
length a $&biglen a1 $&buflen b $&biglen;
retain a;
x = index(a,'ACMT');
do while (x = 0 and not done);
  input a1 $100.;
  a = trim(a) !! a1;
  x = index(a,'ACMT');
end;

So now you can control the length from the top of the code, and won't have the mistake I made with the input a1 $3000.

As for automating the input, lets check what differentiates the wanted output variables:

- name

- position

- type (character or numeric)

- informat for numeric vars (we can "pack" this into "type")

- length or size of input field

so we start our macro definition with

%macro do_input(name,position,type,len);

Then we deal with characters:

%if "&type" = "char" %then %do;
length &name $&len;
&name = scan(b,&position,'^T');
%end;

Next, let's do dates:

%else %if "&type" = "date" %then %do;
length &name 8;
format &name datetime&len..;
&name = input(scan(b,&position,'^T'),ANYDTDTM&len..);
%end;

Now, numbers:

%else %if "&type" = "num" %then %do;
length &name 8;
&name = input(scan(b,&position,'^T'),&len);
%end;

Note that &len would be handed over to the macro as 20.2 in the case of your amount.

Now, let's deal with a possible mistake:

%else %put "Wrong type: &type.!";

and close the macro

%mend;

Now you only have to write one line for each variable in the output data:

%do_input(bank_code,1,char,14);

The next step would be to store the parameters in a dataset that contains metadata about your file structure, and use CALL EXECUTE in a data step to create the data step that splits the lines automatically from the metadata.

For this, I would advise you to read the CALL EXECUTE documentation and start playing around with creating simple data steps at first, and then expand to data steps where repeating lines are created from metadata, and then how to call macros with call execute (hint: use single quotes when you write the macro call).

[Abhi1212]

Your are really great. Thank you sir. Will try wat u said.

[Kurt_Bremser]

Oh, and the buffer size of 100 was deliberately chosen to make my data step iterate, so that start and end conditions and the correct iteration could be proven. For better performance with larger input files, the buffer sizes should be increased to reduce the number of reads.

Also note the use of TRUNCOVER instead of MISSOVER. Read the documentation of the INFILE statement to see the difference.