macro to read all files together

[woo]

Hello Friends, I need proc import help in macro please...I am using below code to read all text files with DLM="|" using 9.1.3 EG.

Please help....!!!

I have text files name bigger than 32 char length so need help on that too...

directory: d:\woo\test\

files under directories are like this;

sales_09765856454_abc_20140716_test_file_1.txt

finance_85876585648564_abc_20140716_test_file_2.txt

sales_8975765856464_xyz_20140716_test_file_3.txt

finance_8975765856464_xyz_20140716_test_file_4.txt

/*----------issues i am having so far are

- file name is invalid - may be due to having long name

- not proper import statement i guess - DLM="|" is not vallid and error message saying "statement not valid"

*/

/*----------------------------------------code------------------------------*/

options mlogic mprint symbolgen;

libname test 'd:\shares\woo';

%macro drive (dir,ext);

%let filrf=mydir;

%let rc=%sysfunc(filename(filrf,&dir));

%let did=%sysfunc(dopen(&filrf));

%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt;

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

%let dslist=;

    %if (%superq(ext) ne and %qupcase(&name)=%qupcase(&ext))or

    (%superq(ext)= and %superq(name)ne) %then %do;

%let file=%qsysfunc(dread(&did,&i));

%let dslist=&dslist %scan(&file,1,.);

       proc import datafile="&dir.%unquote(&file)" out=%scan(%unquote(&file),1,.)

           dbms=dlm

           replace;

            dlm="|";

           /*guessingrows=100;*/ /*i think this option is not available for 9.1.3*/

           getnames=yes;

       run;

     %end;

   %end;

%end;

%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive (d:\woo\test\,txt);

/*Thanks*/

[Reeza]

How would you like to shorten the file name?

Can you read all Sales files into one file and all Finance files into one file or do you need them separately?

[woo]

thanks Reeza for your quick response but i need datasets separately...if we cannot do that then i can minimize the file names somehow, like this;

sales_abc_20140716_test_file_1.txt

finance_abc_20140716_test_file_2.txt

sales_xyz_20140716_test_file_3.txt

finance_xyz_20140716_test_file_4.txt

[Peter_C]

Would the txt files all have the same structure?

Is the structure stable?

[woo]

i don't have it at this moment but can work on it...would this worked?

sales_abc_20140716_test_file_1.txt

finance_abc_20140716_test_file_2.txt

sales_xyz_20140716_test_file_3.txt

finance_xyz_20140716_test_file_4.txt

[woo]

actually even all files has different numbers of variables...just for information...

[KenDodds]

Use delimiter='|'; instead of dlm=...

Your other problem may be due to a missing &  in front of filrf in (filename(filrf,&dir));

The fileref in a filename statement should be no more than 8 characters. I don't think it is the length of the file name (in windows) that is the issue.

Hope this helps.

[Tom · Accepted solution]

1) Make a dataset with the list of files.

2) Use the list of files to generate the code to import each file. Avoid all of the macro logic if you can .

%let dir = d:\woo\test\ ;

%let ext = txt ;

data files ;

  infile "dir &dir.*.&ext /b" pipe truncover ;

  input filename $256.;

  length dsname $32 ;

  dsname = scan(filename,1,'.');

run;

filename code temp;

data _null_;

  set files ;

  file code ;

  put "proc import datafile='" "&dir" filename +(-1) "'"

    / '  out=' dsname 'dbms=dlm replace;'

    / "  delimiter='|';"

    / '  getnames=yes;'

    / 'run;'

  ;

run;

%include code / source2 ;

[woo]

Hi Tom - i am running this code but below piece of code giving libname statement error by reading "dsname" as libname

so error is coming like;

ERROR: Libanme 'sales_abc_20140716_test_file_1' exceed 8 characters.

[Tom]

When you define a library reference you need to supply an 8 character name to use when referencing objects in the library.

Libname LIB00001 'sales_abc_20140716_test_file_1' ;

Should work assuming that there is a directory with that name in the current working directory.

[woo]

Sorry Tom my bed - it ran fine...by mistake i was using my different dataset to create final dataset...

Thanks a lot...Tom...

But if you can explain below part and why we used "/" for each line...and what is the meaning of /source2

filename +(-1) "'"

Appreciate your time...

[Tom]

When writing values in list mode the PUT statement always appends an extra blank after the value.  The +(-1) is a pointer control to move the pointer back one space so that the closing quote will overwrite the extra space that the PUT statement wrote.

The / in a PUT statement tells it to start a new line. I used them so that the generated code is readable by humans.

[woo]

Thanks Tom...!!!