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*/
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 ;
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?
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
Would the txt files all have the same structure?
Is the structure stable?
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
actually even all files has different numbers of variables...just for information...
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.
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 ;
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.
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.
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...
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.
Thanks Tom...!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: