topic Re: macro to read all files together in SAS Enterprise Guide

macro to read all files together

woo — Wed, 16 Jul 2014 18:31:45 GMT

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;

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

%mend drive;

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

/*Thanks*/

Re: macro to read all files together

Reeza — Wed, 16 Jul 2014 19:03:49 GMT

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?

Re: macro to read all files together

woo — Wed, 16 Jul 2014 19:11:08 GMT

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

Re: macro to read all files together

Peter_C — Wed, 16 Jul 2014 19:50:57 GMT

Would the txt files all have the same structure?

Is the structure stable?

Re: macro to read all files together

woo — Wed, 16 Jul 2014 20:19:32 GMT

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

Re: macro to read all files together

woo — Wed, 16 Jul 2014 21:22:37 GMT

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

Re: macro to read all files together

KenDodds — Wed, 16 Jul 2014 22:18:16 GMT

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.

Re: macro to read all files together

Tom — Thu, 17 Jul 2014 01:29:37 GMT

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 ;

Re: macro to read all files together

woo — Thu, 17 Jul 2014 16:44:02 GMT

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.

Re: macro to read all files together

woo — Thu, 17 Jul 2014 16:50:13 GMT

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

Re: macro to read all files together

Tom — Thu, 17 Jul 2014 16:52:08 GMT

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.

Re: macro to read all files together

Tom — Thu, 17 Jul 2014 16:54:15 GMT

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.

Re: macro to read all files together

woo — Thu, 17 Jul 2014 17:30:20 GMT

Thanks Tom...!!!