DATA Step, Macro, Functions and more

convert large number of text files into SAS all at once

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

convert large number of text files into SAS all at once

Hi,

I just learned SAS programming language by reading a book this month.

I have to convert a few hundred tilde delimited text files into SAS format.  Is there a way to do that?

For now, I am converting them using PROC IMPORT one at the time...there must have been a more efficient way to do that.

Thanks for your answers Smiley Happy

Cyndia


Accepted Solutions
Solution
‎04-07-2012 07:52 AM
Super Contributor
Posts: 1,636

Re: convert large number of text files into SAS all at once

%let file_location=c:\temp\; /*where files are located*/

filename indata pipe "dir &file_location.*.txt /b"; /*find the files*/

data _null_;

  length fname fname_wo_ext $32;

  infile indata truncover;

  input fname $32.; /* input the file names */

  fname_wo_ext=cats('_',scan(fname,1,'.'));

  call execute(

     "proc import out= work."||fname_wo_ext

           ||" datafile= "||"'&file_location."||fname||"'

            dbms=dlm replace ;

            delimiter='~';

            getnames=yes;

       run;");

run;

Message was edited by: Linlin

View solution in original post


All Replies
Contributor
Posts: 37

convert large number of text files into SAS all at once

there is no need to merge or combine any files.

Regular Contributor
Posts: 233

convert large number of text files into SAS all at once

http://www2.sas.com/proceedings/sugi29/057-29.pdf

I found a paper for your requirement. Hoping this is what you are looking for.

Contributor
Posts: 37

convert large number of text files into SAS all at once

Thank you Hima.Smiley Happy  I am going to try it and let you know.

PROC Star
Posts: 7,485

convert large number of text files into SAS all at once

Depending upon the kind of computer you are on, where the files are located, how the files are called, whether they all contain a first line of variable names, and where you want the new SAS files placed, the following might help.

If you are on Windows, all of your files have a .txt extension and are all located in the same directory, all contain a first line of variable labels, and you want them all converted to SAS files in a certain directory, you could use something like:

%let file_location=c:\art\test\; /*where files are located*/

filename indata pipe "dir &file_location.*.txt /b"; /*find the files*/

libname mydata "c:\art\mydata"; /*where to put the SAS datasets*/

data data_null_;

  length fname $32;

  length fname_wo_ext $29;

  infile indata truncover;

  input fname $20.; /* input the file names */

  fname_wo_ext=substr(fname,1,length(fname)-4);

  call execute(

   "Proc import out=mydata." || strip(fname_wo_ext)

      || " dataFile="||"'&file_location."

      ||fname||"' dbms=dlm REPLACE ;"

      ||'delimiter="~";GETNAMES=YES ; RUN ;'

    ) ;

run;

Contributor
Posts: 37

Re: convert large number of text files into SAS all at once

i got the following error message:

4  !+            ' dbms=dlm REPLACE ;delimiter=",";GETNAMES=YES ; RUN ;

ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,

              DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Since the original text file names all begin with numbers, e.g. 20120101_testdata.txt, how to prefix an '_' to all the converted  *.sas files?

||'_' somewhere to the beginning of the new *.sas file names?

Contributor
Posts: 37

Re: convert large number of text files into SAS all at once

Any ideas? 

Super Contributor
Posts: 1,636

Re: convert large number of text files into SAS all at once

Hi,

If you want to try my code, first save the text below to c:\temp as

20120101_testdate.txt

20120201_testdate.txt

20120301_testdate.txt

Region~State~Month~Expenses~Revenue

Southern~GA~JAN97~2000~8000

Southern~GA~FEB97~1200~6000

Southern~FL~FEB97~8500~11000

Northern~NY~FEB97~3000~4000

Northern~NY~MAR97~6000~5000

Southern~FL~MAR97~9800~13500

Northern~MA~MAR97~1500~1000

/*After run my code, you should have 3 datasets

_20120101_testdate.sas7bdat,

_20120201_testdate.sas7bdat,

_20120301_testdate.sas7bdat

Created in your work library.*/

%macro test(location);/* location is where the files are stored */

filename indata pipe "dir &location.\*.txt /b"; /*find the files*/

data files;

  length in_name out_name $32;

  infile indata truncover;

  input in_name $32.; /* input the file names */

  out_name=cats('_',scan(in_name,1,'.'));

run;

options mprint mlogic;

data _null_;

  set files end=last;

  call symputx(cats('dsn',strip(_n_)),in_name);

  call symputx(cats('outdsn',strip(_n_)),out_name);

  if last then call symputx('n',_n_);

run;

   %do i=1 %to &n;

       proc import datafile="&location.\&&dsn&i." out= work.&&outdsn&i.

            dbms=dlm replace ;

            delimiter="~";

            getnames=yes;

       run;

%end;

%mend;

%test(c:\temp)

Linlin

Contributor
Posts: 37

Re: convert large number of text files into SAS all at once

i will try this, too.  thanks to linlin :smileycool:

PROC Star
Posts: 7,485

Re: convert large number of text files into SAS all at once

You can use the cats function to add a prefix.  e.g.:

%let file_location=c:\art\test\; /*where files are located*/

filename indata pipe "dir &file_location.*.txt /b"; /*find the files*/

libname mydata "c:\art\mydata"; /*where to put the SAS datasets*/

data data_null_;

  length fname $32;

  length fname_wo_ext $29;

  infile indata truncover;

  input fname $32.; /* input the file names */

  fname_wo_ext=cats('_',substr(fname,1,length(fname)-4));

  call execute(

   "Proc import out=mydata." || strip(fname_wo_ext)

      || " dataFile="||"'&file_location."

      ||fname||"' dbms=dlm REPLACE ;"

      ||'delimiter="~";GETNAMES=YES ; RUN ;'

    ) ;

run;

Contributor
Posts: 37

Re: convert large number of text files into SAS all at once

lemme try them and let you know.  thx again, art297.

i haven't read about SAS macros yet.:smileymischief:

Solution
‎04-07-2012 07:52 AM
Super Contributor
Posts: 1,636

Re: convert large number of text files into SAS all at once

%let file_location=c:\temp\; /*where files are located*/

filename indata pipe "dir &file_location.*.txt /b"; /*find the files*/

data _null_;

  length fname fname_wo_ext $32;

  infile indata truncover;

  input fname $32.; /* input the file names */

  fname_wo_ext=cats('_',scan(fname,1,'.'));

  call execute(

     "proc import out= work."||fname_wo_ext

           ||" datafile= "||"'&file_location."||fname||"'

            dbms=dlm replace ;

            delimiter='~';

            getnames=yes;

       run;");

run;

Message was edited by: Linlin

Contributor
Posts: 45

Re: convert large number of text files into SAS all at once

I made this code work on some sample files. I'm sure someone who does this kind of work all the time could refine/simplify the code...but it did work for me.

I got started from this link:

http://www2.sas.com/proceedings/sugi29/057-29.pdf

and using the answer @art297 provided

Options symbolgen mlogic mprint ;
%macro Get_Files;
  %Let folder=d:\data\test;
  filename indata pipe "dir &folder. /b";

  /* Get the filenames from the pipe directory command */

  data file_list;
   length fname $50;
   infile indata truncover; /* infile statement for file names */
   input fname $50.;  /* read the file names from the directory */
   dsn_name=scan(fname,1,"."); /*Assuming there is an extension on filename (ex .txt)*/
  run;

  /* Get filenames into macro variables */
  Proc Sql noprint;
   select count(fname)
   into :cnt
   from work.file_list;

   %Let cnt=&cnt;

   select fname, dsn_name
   into :fname1 - :fname&cnt, :dsn1 - :dsn&cnt
   from work.file_list;
quit;

/* Loop through all file names to import */
%Do i = 1 %to &cnt;
%Let file_name=&&fname&i;
%Let dsn_name=&&dsn&i;

Proc import out=work._&dsn_name
   dataFile="&folder.\&file_name" dbms=dlm REPLACE ;
   delimiter="~";
   GETNAMES=YES ;
  RUN ;
%end;

%Mend get_files;

%get_Files

Message was edited by: Jay Corbett

Contributor
Posts: 37

Re: convert large number of text files into SAS all at once

Posted in reply to Jay_TxOAG

thanks, Jay.

i read the pdf doc "A Macro for Reading Multiple Text Files" and was able to get the list of the files, but had trouble getting the rest of the code to work.  will try it.Smiley Happy

Contributor
Posts: 37

Re: convert large number of text files into SAS all at once

The solutions you provided turned out to be great.  They work seamlessly for the text files without extra spaces.

How did you learn SAS macro?  I am going to learn macro next.  Any book(s) you would recommend?  Big thanks for all Smiley Wink

🔒 This topic is solved and locked.

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

Discussion stats
  • 16 replies
  • 5157 views
  • 7 likes
  • 6 in conversation