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
%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
there is no need to merge or combine any files.
http://www2.sas.com/proceedings/sugi29/057-29.pdf
I found a paper for your requirement. Hoping this is what you are looking for.
Thank you Hima. I am going to try it and let you know.
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;
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?
Any ideas?
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
i will try this, too. thanks to linlin :smileycool:
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;
lemme try them and let you know. thx again, art297.
i haven't read about SAS macros yet.:smileymischief:
%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
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
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.
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
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: