Desktop productivity for business analysts and programmers

macro to read all files together

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 318
Accepted Solution

macro to read all files together

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*/


Accepted Solutions
Solution
‎07-16-2014 09:29 PM
Super User
Super User
Posts: 6,323

Re: macro to read all files together

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 ;

View solution in original post


All Replies
Grand Advisor
Posts: 17,332

Re: macro to read all files together

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?

Super Contributor
Super Contributor
Posts: 318

Re: macro to read all files together

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

Valued Guide
Posts: 2,174

Re: macro to read all files together

Would the txt files all have the same structure?

Is the structure stable?

Super Contributor
Super Contributor
Posts: 318

Re: macro to read all files together

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

Super Contributor
Super Contributor
Posts: 318

Re: macro to read all files together

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

Occasional Contributor
Posts: 7

Re: macro to read all files together

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.

Solution
‎07-16-2014 09:29 PM
Super User
Super User
Posts: 6,323

Re: macro to read all files together

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 ;

Super Contributor
Super Contributor
Posts: 318

Re: macro to read all files together

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.

Super User
Super User
Posts: 6,323

Re: macro to read all files together

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.


Super Contributor
Super Contributor
Posts: 318

Re: macro to read all files together

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...

Super User
Super User
Posts: 6,323

Re: macro to read all files together

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.

Super Contributor
Super Contributor
Posts: 318

Re: macro to read all files together

Thanks Tom...!!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1743 views
  • 0 likes
  • 5 in conversation