topic Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row in SAS Programming

infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Wed, 16 Aug 2017 09:39:03 GMT

I have a infile code that work for 1 txt file, recently want to enhance it to infile more than 1 txt file into 1 dataset. Code can proceeded and i'm facing difficulty to slot in 1 programming command(to delete empty and unwanted row) as result shown 1st record read and then it jump to 2nd txt file, instead of contnue till end of the 1st txt file.

my code as below:-

filename fn pipe 'dir C:\Users\Desktop\testing\*.txt /s /b';

data new;
infile fn;
input name:&$100.;

infile in filevar=name end=last firstobs=11 missover dsd ;

retain Grp_AC    No	Batch_no	TRX_CODE   Amount	Block_Code ; 

format Grp_AC $char11.
	   Batch_no $char14.
	   Block_Code $char4.
	   No $char16.
	   TRX_CODE $char3.;

do while(not last);

Input  Grp_AC $ 1-24 @;
file_name = scan( scan( name, -1, '\' ), 1, '.' );

input    Grp_AC $ 1-24    No $ 31-48  Batch_no $  52-66   TRX_CODE $ 72-76  @86   Amount  trailsgn16. Block_Code  $ 110-113 ;

output;
end;
run;

wanna to insert code below:-

if Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count'  then delete;

Thank in advance.

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Shmuel — Wed, 16 Aug 2017 10:15:25 GMT

Assuming all input text files have the same format and should be treated the same

enter your code - dealing with the variables just read - just before the OUTPUT statement.

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Wed, 16 Aug 2017 10:52:02 GMT

Hi Shmuel,

Did try below, but it only read 1 record from each txt file. Think the delete stop the current process and went to next txt file

filename fn pipe 'dir C:\Users\Desktop\testing\*.txt /s /b';

data new;
infile fn;
input name:&$100.;

infile in filevar=name end=last firstobs=11 missover dsd ;

retain Grp_AC    No	Batch_no	TRX_CODE   Amount	Block_Code ; 

format Grp_AC $char11.
	   Batch_no $char14.
	   Block_Code $char4.
	   No $char16.
	   TRX_CODE $char3.;

do while(not last);

Input  Grp_AC $ 1-24 @;
file_name = scan( scan( name, -1, '\' ), 1, '.' );

if Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count'  then delete;

input    Grp_AC $ 1-24    No $ 31-48  Batch_no $  52-66   TRX_CODE $ 72-76  @86   Amount  trailsgn16. Block_Code  $ 110-113 ;

output;
end;
run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Reeza — Wed, 16 Aug 2017 12:15:19 GMT

See an example here

Use EOV on the INFILE to identify the first record.

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Shmuel — Wed, 16 Aug 2017 12:45:04 GMT

Try next code:

filename fn pipe 'dir C:\Users\Desktop\testing\*.txt /s /b';

data new;
infile fn  EOV=Done;  /*** Option EOD added ***/
input name:&$100.;

infile in filevar=name end=last firstobs=11 missover dsd ;

retain Grp_AC    No	Batch_no	TRX_CODE   Amount	Block_Code ; 

format Grp_AC $char11.
	   Batch_no $char14.
	   Block_Code $char4.
	   No $char16.
	   TRX_CODE $char3.;

do while(not last);

Input  Grp_AC $ 1-24 @;
file_name = scan( scan( name, -1, '\' ), 1, '.' );

input    Grp_AC $ 1-24    No $ 31-48  Batch_no $  52-66   TRX_CODE $ 72-76  @86   Amount  trailsgn16. Block_Code  $ 110-113 ;

/*** place of the if changed ***/
if Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count'  then delete;


output;
end;
run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Tom — Wed, 16 Aug 2017 13:20:20 GMT

You don't want to use the DELETE statement since that will end the data step iteration and start from the next file in the input list.

So instead of

if (condition) then delete;

You want to code

if not (condition) then output;

So your program might look like this:

data new;
  infile 'dir C:\Users\Desktop\testing\*.txt /s /b' pipe truncover;
  length Grp_AC $11 No $16 Batch_no $14 
         TRX_CODE $3 Amount 8 Block_Code $4 
  ;
  input name $256. ;
  file_name = scan( scan( name, -1, '\' ), 1, '.' );
  infile in filevar=name end=last firstobs=11 truncover  ;
  do while (not last);
    input  Grp_AC $ 1-24 @;
    if  not (Grp_AC in ('Grp A' , ' ', 'OVER', 'Total Count') ) then do;
      input Grp_AC $ 1-24
            No $ 31-48
            Batch_no $  52-66   
            TRX_CODE $ 72-76  
            @86   Amount  trailsgn16. 
            Block_Code  $ 110-113 
      ;
      output;
    end;
  end;
run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

TomKari — Wed, 16 Aug 2017 15:44:29 GMT

Reply number one:

A lot of managed SAS environments won't let you use the "X" statement, so here's an alternate, really easy way to get all of the datasets in a directory. You just need to add a bit of code to select only the ".txt" datasets.

/* Set the directory name to scan */

%let DirectoryName = C:\Users\Desktop\testing;

data DatasetsInADirectory;

keep DirectoryAndDataset;

length DatasetName DirectoryAndDataset $1024 FileRef $8;

/* Assign the fileref */

call missing(FileRef); /* Blank, so SAS will assign a file name */

rc1 = filename(FileRef, "&DirectoryName."); /* Associate the file name with the directory */

if rc1 ^= 0 then

abort;

/* Open the directory for access by SAS */

DirectoryID = dopen(FileRef);

if DirectoryID = 0 then

abort;

/* Get the count of directories and datasets */

MemberCount = dnum(DirectoryID);

if MemberCount = 0 then

abort;

/* Get all of the entry names ... directories and datasets */

do MemberIndex = 1 to MemberCount;

DatasetName = dread(DirectoryID, MemberIndex);

if missing(DatasetName) then

abort;

DirectoryAndDataset = cats("&DirectoryName.","/",DatasetName);

output;

end;

/* Close the directory */

rc2 = dclose(DirectoryID);

if rc2 ^= 0 then

abort;

run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

TomKari — Wed, 16 Aug 2017 16:14:00 GMT

Reply number two

Ordinarily, I hesitate to recommend SAS macros to less-experienced users. But I also hate to see "do until" type processing of input datasets.

So here's the simplest possible macro which will give you similar results. You'll notice it has the advantage that although there are more steps, each step is extremely simple, and stands alone.

It uses the results of my first reply.

Tom

%macro GetTXTData(DsnToGet);

data temp;

infile "&DsnToGet."; /* You had additional infile options, which would go here */

Input Grp_AC $; /* Again, change as required */

/* additional processing here */

run;

proc datasets lib=work nolist;

append data=temp base=new;

delete temp;

quit;

%mend;

proc datasets lib=work nolist;

delete new;

quit;

data _null_;

length SASMacroCall $1024;

set DatasetsInADirectory;

SASMacroCall = %nrstr(cats("%GetTXTData(", strip(DirectoryAndDataset), ");"));

call execute(SASMacroCall);

run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 06:18:54 GMT

Hi Reeza/shmuel,

thank for the code and guidance, i actually have 4 txt file, try below:-

filename fn pipe 'dir C:\Users\Desktop\testing\*.txt /s /b';

data new;
infile fn  EOV=Done;  /*** Option EOD added ***/
input name:&$100.;

infile in filevar=name end=last firstobs=11 missover dsd ;

retain Grp_AC    No	Batch_no	TRX_CODE   Amount	Block_Code ; 

format Grp_AC $char11.
	   Batch_no $char14.
	   Block_Code $char4.
	   No $char16.
	   TRX_CODE $char3.;

do while(not last);

Input  Grp_AC $ 1-24 @;
file_name = scan( scan( name, -1, '\' ), 1, '.' );

input    Grp_AC $ 1-24    No $ 31-48  Batch_no $  52-66   TRX_CODE $ 72-76  @86   Amount  trailsgn16. Block_Code  $ 110-113 ;

/*** place of the if changed ***/
if Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count'  then delete;


output;
end;
run;

only get total 4 record from 4 txt file.

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 06:29:49 GMT

Hi Tom,

Try run the code and it take long time to process(1 hour waited) and still running. I stop the code running and check the log, seem it stuck at 2nd record.

data new;
  infile 'dir C:\Users\Desktop\testing\*.txt /s /b' pipe truncover;
  length Grp_AC $11 No $16 Batch_no $14 
         TRX_CODE $3 Amount 8 Block_Code $4 
  ;
  input name $256. ;
  file_name = scan( scan( name, -1, '\' ), 1, '.' );
  infile in filevar=name end=last firstobs=11 truncover  ;
  do while (not last);
    input  Grp_AC $ 1-24 @;
    if  not (Grp_AC in ('Grp A' , ' ', 'OVER', 'Total Count') ) then do;
      input Grp_AC $ 1-24
            No $ 31-48
            Batch_no $  52-66   
            TRX_CODE $ 72-76  
            @86   Amount  trailsgn16. 
            Block_Code  $ 110-113 
      ;
      output;
    end;
  end;
run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Shmuel — Thu, 17 Aug 2017 07:02:36 GMT

Would you try:

filename fn pipe 'dir C:\Users\Desktop\testing\*.txt /s /b';

data new;
infile fn  EOV=Done;  
input name:&$100.;

infile in filevar=name end=last firstobs=11 missover dsd ;

retain Grp_AC    No	Batch_no	TRX_CODE   Amount	Block_Code ; 

format Grp_AC $char11.
	   Batch_no $char14.
	   Block_Code $char4.
	   No $char16.
	   TRX_CODE $char3.;

DO UNTIL (EOV);   /* line added */

do while(not last);

Input  Grp_AC $ 1-24 @;
file_name = scan( scan( name, -1, '\' ), 1, '.' );

input    Grp_AC $ 1-24    No $ 31-48  Batch_no $  52-66   TRX_CODE $ 72-76  @86   Amount  trailsgn16. Block_Code  $ 110-113 ;

/*** statement changed according to @Tom's suggestion ***/
if  NOT (Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count')
  then output;
end;

last=0; /* reseting for next file */
END;    /* ending loop until eov */
run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 07:06:49 GMT

Hi TomKari,

Thank for your code, tried the code and it actually import the data to SAS. Problem is how do I get rid of unwanted row like blank and header.

below is those row I don't want:-

Grp_AC = 'Grp A'

Grp_AC = ''

Grp_AC = 'OVER'

Grp_AC = 'Total Count'

where should I insert those code, assume my code as below or any other alternative.

if Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count' then delete;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 07:20:08 GMT

Hi Shmuel,

try the code it actually infile 1 txt file only, then it stop, won't proceed to other txt file. There another variable name "EOV" appear in dataset which this variable not exist in txt file.

Thanks

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Shmuel — Thu, 17 Aug 2017 07:40:37 GMT

1) have you got all rows from the first text file?

2) soory, I had a typo. I defined

infile fn  EOV=Done;

therefore change from:

DO UNTIL (EOV);

DO UNTIL (Done);

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 07:56:14 GMT

Hi Shmuel,

Yes,I have all rows i wanted and EOV variable no more. Now how to import other txt file as now it only import 1 instead of 4 txt file.

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Shmuel — Thu, 17 Aug 2017 08:22:49 GMT

I cannot simulate and run your code nor guess where is the problem.

I believe it should read all 4 files.

Have you reset last=0; between end of 'do until(last)' and end of 'do until(done)'.

please post the full log running with options source;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 08:57:05 GMT

Hi Shmuel,

Apologize for inconveniece caused, attached the 2 txt file and code.

filename fn pipe 'dir C:\Users\Desktop\testing\*.txt /s /b';

data new;
infile fn  EOV=Done;  
input name:&$100.;

infile in filevar=name end=last firstobs=11 missover dsd ;

retain Grp_AC  No	Batch_no	TRX_CODE   Amount	Block_Code ; 

format Grp_AC $char11.
	   Batch_no $char14.
	   Block_Code $char4.
	   No $char16.
	   TRX_CODE $char3.;

DO UNTIL (Done);    /* line added */

do while(not last);

Input Grp_AC $ 1-24 @;
file_name = scan( scan( name, -1, '\' ), 1, '.' );

input    Grp_AC $ 1-24    No $ 31-48  Batch_no $  52-66   TRX_CODE $ 72-76  @86   Amount  trailsgn16. Block_Code  $ 110-113 ;

/*** statement changed according to @Tom's suggestion ***/
if  NOT (Grp_AC = 'Grp A' or Grp_AC = '' or Grp_AC = 'OVER' or Grp_AC = 'Total Count')  then output;
end;last=0; /* reseting for next file */
END;    /* ending loop until eov */
run;

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 09:02:50 GMT

Hi Shmuel,

Attached the log summary(screenshot as well).

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

Shmuel — Thu, 17 Aug 2017 10:03:48 GMT

In your summary log the is a message of LOST CARD.

One more try - replace the MISSOVER option with TRUNCOVER option

with hope it will help, otherwise maybe someone else will be able to help.

Re: infile mutiple txt file, where should i put remove code to remove unwanted row or blank row

sagulolo — Thu, 17 Aug 2017 10:24:35 GMT

Hi Shmuel,

Replace MISSOVER option with TRUNCOVER option but same problem happen. Only 4 records output and read 1 txt file only,

Summary log same message got LOST CARD.