topic Re: Multiple text files into one Sas dataset using macro in SAS Programming

Multiple text files into one Sas dataset using macro

deleted_user — Tue, 31 Mar 2009 19:47:16 GMT

Basically I am trying to create a dataset that contains a little over 300 text files. The file names range from hclr1.txt to hclr314.txt. They all have the same variables and are aligned neatly in columns. Is there a way to read in all these files using a do loop and macro to read all files rather than making an infile statement from each and merging later?? Any help would be appreciated. Thanks

Re: Multiple text files into one Sas dataset using macro

deleted_user — Tue, 31 Mar 2009 20:09:35 GMT

Some of these txt files contain the variable day with monday wednesday friday and others contain monday tuesday wednesday thursday and friday. I am not sure how this will affect the data when it is merged??

Re: Multiple text files into one Sas dataset using macro

LinusH — Tue, 31 Mar 2009 20:17:32 GMT

Yes you may. Just use the loop macro variable in the filename. After reading the file , just PROC APPEND it to a common table.

I'm not sure why you are concerned about the day variable. It will just a ordinary column in the result table...?

/Linus

Re: Multiple text files into one Sas dataset using macro

deleted_user — Tue, 31 Mar 2009 20:36:27 GMT

I am going to end up sorting by day of th week. I tried merging the data (I just tried 3 files at the moment) and when I merged the files with Monday-Friday with the Monday Wednesday Friday it seems to have messed up.

An example of my data is:

Monday 1 14 3 79 14.07
Wednesday .....
Friday .....

merged with:

Monday .....
Tuesday .....
Wednesday ....
Thursday ....
Friday .....

The problem is that when I merge a dataset that does not contain a Tuesday, all the data from wednesday moves to tuesday and friday is left blank.

As for the macro, I have little knowledge or expierience working with them so really have no clue on the specific language needed to run properly.

Once again thanks for any help.

Re: Multiple text files into one Sas dataset using macro

sbb — Tue, 31 Mar 2009 20:42:39 GMT

If the files are all structured reasonably consistent, at least where you can code one (or more as needed) INPUT statements, why not use the FILENAME and generate the list of files (if each is not too large to process as a single input stream) so that the files are treated as a logical concatenation with a DATA step. The list of files can easily be generated by a MACRO %DO %END loop, incrementing you file-suffix count. Here is the FILENAME syntax, abbreviated:

FILENAME INDATA ("file1.txt" "file2.txt" "file3.txt");

Scott Barry
SBBWorks, Inc.

Re: Multiple text files into one Sas dataset using macro

Cynthia_sas — Tue, 31 Mar 2009 21:23:48 GMT

Hi:
Just to clarify...there is a difference between MERGING datasets and CONCATENATING or APPENDING datasets.

Here's an example....you have a dataset for every month: WORK.JAN, WORK.FEB, WORK.MAR, etc and the data looks like this:
[pre]
file WORK.JAN
day sale month
01 101 01
02 102 01
03 103 01

file WORK.FEB
day sale month
01 201 02
02 202 02
03 203 02

file WORK.MAR
day sale month
01 301 03
02 302 03
03 303 03
[/pre]

and you want to "stack" or "append" all the files together so that you have an entire year. That syntax would be:
[pre]
data allyear;
set work.jan work.feb work.mar work.apr work.may work.jun
work.jul work.aug work.sep work.oct work.nov work.dec;
run;
[/pre]

(or you could use PROC APPEND or PROC SQL,) but for now, let's stick with a data step program. The output in WORK.ALLYEAR would now look like this (only a few obs shown for each month):
[pre]
file WORK.ALLYEAR
day sale month
01 101 01
02 102 01
03 103 01
01 201 02
02 202 02
03 203 02
01 301 03
02 302 03
03 303 03
[/pre]

Note how the data are "stacked" one month on top of the next month and so on. This would be concatenating or appending.

Or, you could join the data "horizontally". Consider this data for every month (only a few obs are shown) the day variable identifies the day of the month the sale variable for each month, for ease of the example, is named jansale, febsale, marsale, etc:

[pre]
file WORK.JAN
day jansale
01 101
02 102
03 103

file WORK.FEB
day febsale
01 201
02 202
03 203

file WORK.MAR
day marsale
01 301
02 302
03 303
[/pre]

If the data were joined horizontally, you would get this:
[pre]

file WORK.ALLYEAR
day jansale febsale marsale
01 101 201 301
02 102 202 302
03 103 203 303
.... more observations ....
25 125 225 325
26 126 226 326
27 127 227 327
28 128 228 328
29 129 . 329
30 130 . 330
31 131 . 331

[/pre]

Note how the end of the month shows missing values for days 29, 30 and 31 for Feb (because Feb did not have any sales on those days).

The code that created the above output is shown below:
[pre]

data allyear;
merge jan(in=injan)
feb(in=infeb)
mar(in=inmar);
by day;
run;

proc print data=allyear;
title 'after merge';
run;
[/pre]

If you are appending, then not having Tuesday in one file should not make a difference. If you are merging, using a data step, then you should merge with a BY statement and if you are joining with PROC SQL, then you have to be careful with the type of join you code so you get the right combination of rows and columns in the output file.

cynthia

Re: Multiple text files into one Sas dataset using macro

deleted_user — Tue, 31 Mar 2009 22:51:12 GMT

Thank you, that was very helpful.

I am still confused on how to import the 300 txt files though. I currently have a macro in which I have n=0 and then n=&n + 1 where i have my file names hclr&n.txt, I don't know if this is possible and it doesn't seem to be working. I just need it to read in my files and each has the same variable name and each file is just one number higher than the last.

Re: Multiple text files into one Sas dataset using macro

data_null__ — Tue, 31 Mar 2009 23:21:41 GMT

I suggest you look at the documentation for the INFILE statement. You will want to pay particular attention to the FILEVAR option. You will need read about the INPUT statement. See the following thread for and example I posted using INFILE and FILEVAR etc.

[pre]
http://support.sas.com/forums/thread.jspa?messageID=18256䝐
[/pre]

Re: Multiple text files into one Sas dataset using macro

LinusH — Wed, 01 Apr 2009 06:45:42 GMT

It seems that you need to acquire some basic skills in macro programming. I suggest that you take a class in this subject. It's available as a e-course:

http://www.sas.com/apps/elearning/elearning_courses.jsp?cat=SAS+Advanced+Programming

or as a traditional instructor lead one at your nearest SAS office.

Regards,
Linus

Re: Multiple text files into one Sas dataset using macro

deleted_user — Thu, 02 Apr 2009 14:20:59 GMT

OK, well this is my code I have so far:

%MACRO workdata;
%DO a=1 %TO 314;
DATA hclr&a;
INFILE 'C:users\work\documents\work\hclr&a..txt';
INFORMAT ..... ;
INPUT ....;
RUN;

PROC SORT DATA=hclr&a;
BY .....;
RUN;

%END;
%MEND workdata;

%workdata;

RUN;

The error is that it says the files can't be found but I thought the hclr&a..txt code should have continously replace &a with the numbers 1 through 314?? This code I thought would work to create 314 data sets that I could later merge. Anyone see any problems with this macro? I am using the right infile statement because it does work when I replace &a with a number. Thanks for any help.

Re: Multiple text files into one Sas dataset using macro

Cynthia_sas — Thu, 02 Apr 2009 14:24:26 GMT

Hi:
Try these 2 title statements and see which one works -- &SYSDATE is an automatic macro variable:
[pre]
title1 'Run on &sysdate';
title2 "Run on &sysdate";
[/pre]

cynthia

Re: Multiple text files into one Sas dataset using macro

sbb — Thu, 02 Apr 2009 15:04:03 GMT

Suggest you add the following statement to your program in order to generate as much diagnostic information as possible in your SAS log output - the second statement (or both of them, as preferred) can be commented out when you have a working macro:

OPTIONS SOURCE SOURCE2;
OPTIONS MACROGEN SYMBOLGEN MLOGIC;

The additional SAS compilation information generated will help you self-diagnose the SAS problem, when using macro language.

Scott Barry
SBBWorks, Inc.

Re: Multiple text files into one Sas dataset using macro

deleted_user — Thu, 02 Apr 2009 17:57:31 GMT

It gave me more information but it still says physical file does not exist. For some reason it is not replacing the &a..txt with the new number of the do loop.

Re: Multiple text files into one Sas dataset using macro

deleted_user — Thu, 02 Apr 2009 18:17:39 GMT

I figured it out. It needed " " instead of ' ' around the infile statement. Thanks for all the Help!!

Re: Multiple text files into one Sas dataset using macro

sbb — Thu, 02 Apr 2009 18:18:35 GMT

You should be able to follow the SAS code compilation and generate SAS statements from your macro. If unable to identify the problem cause, you need to post a reply with your SAS log information (COPY / PASTE) into your reply. Key information is "where your macro variable is assigned" and "where does SAS resolve the code using the macro variable". SAS will reveal the macro variable assignments -- obviously the lines must use an ampersand where referenced.

Scott Barry
SBBWorks, Inc.

x command and data step

WendyT — Tue, 07 Apr 2009 15:24:30 GMT

I have a similar situation. The structure of the files is the same, but the file names are random, and over time, more files are added.

This program uses the x command to go to Windows and generate a file with the names of all *.csv files in a directory. Then control comes back to SAS, and the INFILE statement uses the generated file with the FILEVAR option to read and stack the files.

All you need to know to use this method is the directory and filetype.

I left the length and input statements as is so you could see the structure.

Wendy

filename par_files 'D:\yada\my_files.sas' ;
x 'cd D:\yada\my_directory' ;
x 'del my_files.sas' ;
x 'dir *.csv /a-d /B /L > my_files.sas' ;
x 'exit' ;

DATA PAR_FILES ;

LENGTH
READFILE $ 200
STATION $ 20
PAR1_ $ 10
PAR2_ $ 10
PAR3_ $ 10 ;

FORMAT SASDATE YYMMDD6. ;

INFILE PAR_FILES ;
INPUT READFILE $ ;
INFILE FILE FILEVAR=READFILE END=DONE MISSOVER PAD LRECL=900 FIRSTOBS=4 DLM=',' ;
DO UNTIL (DONE) ;

INPUT
STATION $ SASDATE YYMMDD8. TIME1 REP SET DPTHSTR PAR_K
DPTHCOL1 PAR1 PAR1_ $
DPTHCOL2 PAR2 PAR2_ $
DPTHCOL3 PAR3 PAR3_ $ ;

OUTPUT ;
END ;

RUN ;

Re: Multiple text files into one Sas dataset using macro

deleted_user — Tue, 07 Apr 2009 21:29:48 GMT

SASuser01

because you have SAS you have a simple process to load this data.
It is a data step.
The infile statement (except on ZOS mainframes, which I think is NOT your platform) allow you to define the physical file names with an *, like[pre] infile '\\path\hclr*.txt' ; [/pre]That takes care of all that macro looping. You need only one infile statement. (keep files with similar names that you do not wish to load, in a different folder).
What remains is the code to read your columns. As your files all have the same layout, you need only one set of code to read them.
Since you don't describe your text file in detail, I won't offer code to read it.

good Luck

PeterC

Re: Multiple text files into one Sas dataset using macro

WendyT — Thu, 09 Apr 2009 17:24:35 GMT

PeterC-

That's a MUCH better solution than mine - I'll give it a try.

Thanks for the help!

Wendy