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

convert large number of text files into SAS all at once

Cyndia — Fri, 30 Mar 2012 23:05:39 GMT

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

Cyndia

convert large number of text files into SAS all at once

Cyndia — Fri, 30 Mar 2012 23:11:10 GMT

there is no need to merge or combine any files.

convert large number of text files into SAS all at once

Hima — Fri, 30 Mar 2012 23:24:47 GMT

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

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

convert large number of text files into SAS all at once

Cyndia — Fri, 30 Mar 2012 23:29:39 GMT

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

convert large number of text files into SAS all at once

art297 — Sat, 31 Mar 2012 01:52:42 GMT

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;

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

Cyndia — Mon, 02 Apr 2012 18:40:18 GMT

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?

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

Cyndia — Fri, 06 Apr 2012 16:36:20 GMT

Any ideas?

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

Jay_TxOAG — Fri, 06 Apr 2012 17:47:16 GMT

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

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

Linlin — Fri, 06 Apr 2012 19:38:13 GMT

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

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

art297 — Fri, 06 Apr 2012 20:04:07 GMT

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;

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

Cyndia — Fri, 06 Apr 2012 20:19:24 GMT

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

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

Cyndia — Fri, 06 Apr 2012 20:21:22 GMT

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

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

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

Cyndia — Fri, 06 Apr 2012 20:26:39 GMT

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.

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

Linlin — Sat, 07 Apr 2012 11:52:25 GMT

%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

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

Cyndia — Wed, 11 Apr 2012 15:42:45 GMT

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

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

Jay_TxOAG — Wed, 11 Apr 2012 15:51:07 GMT

I was lucky enough to attend SAS Macro I and II classes to get a good foundation....but the best advice I received about using macros is: Write it first without macros...just duplicate your code as much as is necessary to get it to work....then go back and put in the macro code a little at a time. Honestly, you can learn a lot from SAS's site and using Google...stay with it. Good luck

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

data_null__ — Wed, 11 Apr 2012 17:28:04 GMT

Are the fields the same or similar for all files?

Do you know the field names and data types?

Do you want a single data set in the end. (assuming common fields)?

This paper http://www.lexjansen.com/pharmasug/2011/tt/pharmasug-2011-tt04.pdf includes an example program to read any number of files with common fields. The fields don't have to exist in all files nor be in any particular column but you do have to to know the field names you want to read and define them as to type. In other words you will need to know something about the data you are reading but there is no quessing as with PROC IMPORT.