BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cyndia
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

%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

16 REPLIES 16
Cyndia
Calcite | Level 5

there is no need to merge or combine any files.

Hima
Obsidian | Level 7

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

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

Cyndia
Calcite | Level 5

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

art297
Opal | Level 21

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;

Cyndia
Calcite | Level 5

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?

Linlin
Lapis Lazuli | Level 10

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

Cyndia
Calcite | Level 5

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

art297
Opal | Level 21

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;

Cyndia
Calcite | Level 5

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

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

Linlin
Lapis Lazuli | Level 10

%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

Jay_TxOAG
Quartz | Level 8

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

Cyndia
Calcite | Level 5

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

Cyndia
Calcite | Level 5

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

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!

How to Concatenate Values

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.

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
  • 16 replies
  • 7211 views
  • 7 likes
  • 6 in conversation