SAS macro to read multiple rawdata files and create multiple SAS dataset for each raw data file

Question

Hi there

My name is Chandra. I am not very good at SAS macro especially the looping part and resolving &&. etc. Here is my problem statement.

Problem statement: I have large number of raw data files (.dat files) stored in a folder in a SAS server. I need a macro that can read each of these raw data file and create SAS data set for each raw data file and store them in a separate target folder in the SAS server. All these raw data files have same file layout structure. I need to automate this operation so that every week, the macro reads raw data files from the source folder and creates the corresponding SAS dataset and stores them in the target folder in the SAS server. For example, if there are 200 raw data files in a source folder, I want to read them and create 200 SAS datasets one for each raw data file and save them in a target folder. I am not very good at constructing looping statement and also resolving && or &&& etc. How do I do it?

I would highly appreciate your kind assistance in this regard.

Respectfully

Chandra

How do you know the names of the files? How will you define the names of the datasets? Why do you need to generate multiple datasets, why not just read all of the files into a single dataset? Why do you need a macro instead of just a program? Did you try something already? If so, what? — Tom, Sep 30 '15 at 02:22

score 0 · Answer 1 · answered Sep 30 '15 at 08:57

0

You don't need to necessarily use a macro or a loop in case you have files with same fields. You can try pipe option and the filename keyword. Here is the link

answered Sep 30 '15 at 08:57

Nirvik Banerjee

335
5
16

score 0 · Answer 2 · answered Sep 30 '15 at 12:08

You do not need a macro for this type of processing. The INFILE statment will accept a file specification that includes operating system wildcards.

This example creates 200 text files in the work folder and then reads them back in in a single step.

I highly recommend not creating 200 separate data sets. Instead keep the filename, or a unique portion thereof, as a categorical variable that can be used later in a CLASS or BY statement, or as part of the criteria of a sub-setting WHERE.

%let workpath = %sysfunc(pathname(WORK));

* create something to input;

data _null_;
  do i = 0 to 1999;
    if mod(i,10) = 0 then filename = cats("&workpath./",'sample',i/10,'.txt');
    file sample filevar=filename;
    x = i; y = x**2;
    put i x y;
  end;
run;

* input data from 200 different files that have the same layout;

data samples;
  length filename $250;
  infile "&workpath.\*.txt" filename=filename;  %* <-- Here be the wildcards;
  input i x y;
  source = filename;
run;

SAS macro to read multiple rawdata files and create multiple SAS dataset for each raw data file

2 Answers2