BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

12 REPLIES 12
Reeza
Super User

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
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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
Rhodochrosite | Level 12

Would the txt files all have the same structure?

Is the structure stable?

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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

KenDodds
Calcite | Level 5

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

Thanks Tom...!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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