Architecting, installing and maintaining your SAS environment

Import multiple data files with single click.

Reply
Contributor
Posts: 71

Import multiple data files with single click.

Hi All,

I need your help. I have around 150 data files (CSV files)  with  in different folders.

I wrote a macro to pick the data from the each folder and create data set.

1)The problem  in some of  variable the values are in exponents. I used IMPORT procedure in macro . But default it taking format as best12 for all numeric. So I went for Import wizard. Is there any proces to make all numeric to  best 32. in format so that I can save time.

2) Is there any way to create automatic process to pick files automatically and create data set from the folder I keep all the files in one folder and create a new data set. What I mean was I show the macro the folder the macro must read all the files and create a ew data set for each and every data set. With above case.

Can you give me hint hot to do this using macro.Because in coming days we are going to get huge files .

I'm looking for solution in windows. We are using base sas 9.2V.

Respected Advisor
Posts: 4,919

Re: Import multiple data files with single click.

About question 2: In Windows, you can use wildcards to read multiple files from the same directory. The only difficulty with CSV files is skipping the header line in each file. You may also want to record from which file each observation comes from. The following code takes care of both issues.

filename myCSV "c:\csvPath\*.csv";

data myTable;

infile myCSV dsd missover eov=skip filename=fromFile;

input @;

if skip then skip=0;

else do;

     input myVar1 myVar2 myVar3;

     myVar0 = fromFile;

     output;

     end;

run;

PG

PG
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Import multiple data files with single click.

In the past I used a macro (following the progra from some website that I don't remember). This actually helps.

Please change the macro to cater for your requirement. This is just a guide line.

%macro fileloc(filein);

%let outfilepath=myoutfilepath ;*outfile path name;

%let SourceDatapath=mysourcedata ;*sourcedata path name;

%let filrf=mydir;

%let rc=%sysfunc(filename(filrf,"&root_dir\Source Data\Online Source Data"));

%let did=%sysfunc(dopen(&filrf));

%let fname=;

%let memcount=%sysfunc(dnum(&did));

%if &memcount > 0 %then

%do i=1 %to &memcount;

%let fname=%sysfunc(dread(&did,&i));

%let iw=%index(&fname,.);

%let loc=%substr(&fname,1,%eval(&iw-1)); /* get first part of file name */

filename dr "&SourceDatapath\&fname.";

filename outfile "&outfilepath\&fname."; /* use file name to determine;

directory for output file */

run;

PROC IMPORT OUT= WORK.A

            DATAFILE= "&sourcedatapath\&fname."

            DBMS=TAB REPLACE;

     GETNAMES=No;

     DATAROW=1;

RUN;

%end;

%let rc=%sysfunc(dclose(&did));

%mend fileloc;

%fileloc(&filein1);

Ask a Question
Discussion stats
  • 2 replies
  • 1459 views
  • 0 likes
  • 3 in conversation