topic Re: Import and Append all CSV files in folder - SAS EG in SAS Programming

Import and Append all CSV files in folder - SAS EG

tropical_surfer — Fri, 27 May 2016 19:55:20 GMT

I'm trying to use code to genetrate a list of the csv files in a folder and then run a macro to import and append these files. I've checked other posts similar to this and have gotten close but the import and append code doesn't run successfully (the filelist is created successfully.)

Here's what I have:

data filelist;
keep filename;
length fref $8 filename $80;
rc = filename(fref, "/read_in_checks");
if rc = 0 then
do;
did = dopen(fref);
rc = filename(fref);
end;
else
do;
length msg $200.;
msg = sysmsg();
put msg=;
did = .;
end;
if did <= 0
then
putlog 'ERR' 'OR: Unable to open directory.';
dnum = dnum(did);
do i = 1 to dnum;
filename = dread(did, i);
/* If this entry is a file, then output. */
fid = mopen(did, filename);
if fid > 0
then
output;
end;
rc = dclose(did);

run;

/*list of csv file names is created above and used in macro below, the fiilelist is correct, just cant get the macro below to work.*/

%macro callmacro;

data _null_;set filelist nobs=nobs;
call symput('TotFiles',put(nobs,8.));
run;

%do a=1 %to &&TotFiles;

data _null_;set filelist;
if _n_=&&a;
call symputx('csvdatafilex',put(filename,$32.));
run;

%loops(&&csvdatafilex);

%end;

%mend;
%callmacro

Log Results:

23 GOPTIONS ACCESSIBLE;
24 /* Insert custom code before submitted code here */
25 options dtreset validvarname=v7;
26
27
28 options mprint;
29 %macro callmacro;
30
31 data _null_;set filelist nobs=nobs;
32 call symput('TotFiles',put(nobs,8.));
33 run;
34
35 %do a=1 %to &&TotFiles;
36
37 data _null_;set filelist;
38 if _n_=&&a;
39 call symputx('csvdatafilex',put(filename,$32.));
40 run;
41
42 %loops(&&csvdatafilex);
43
44 %end;
45
46 %mend;
47 %callmacro
MPRINT(CALLMACRO): data _null_;
MPRINT(CALLMACRO): set filelist nobs=nobs;
MPRINT(CALLMACRO): call symput('TotFiles',put(nobs,8.));
MPRINT(CALLMACRO): run;

NOTE: There were 193 observations read from the data set WORK.FILELIST.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
2 The SAS System 14:41 Friday, May 27, 2016

cpu time 0.00 seconds

MPRINT(CALLMACRO): data _null_;
MPRINT(CALLMACRO): set filelist;
MPRINT(CALLMACRO): if _n_=1;
MPRINT(CALLMACRO): call symputx('csvdatafilex',put(filename,$32.));
MPRINT(CALLMACRO): run;

NOTE: There were 193 observations read from the data set WORK.FILELIST.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Line generated by the invoked macro "CALLMACRO".
47 data _null_;set filelist; if _n_=&&a; call symputx('csvdatafilex',put(filename,$32.)); run; %loops(&&csvdatafilex);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro LOOPS not resolved.
MPRINT(CALLMACRO): %loops(ACSIS_HSHLD_HIST_CLM_B_checks.cs);

MPRINT(CALLMACRO): data _null_;
MPRINT(CALLMACRO): set filelist;
MPRINT(CALLMACRO): if _n_=2;
MPRINT(CALLMACRO): call symputx('csvdatafilex',put(filename,$32.));
MPRINT(CALLMACRO): run;

Re: Import and Append all CSV files in folder - SAS EG

Reeza — Fri, 27 May 2016 20:02:35 GMT

You don't need a macro.

Look at the documentation and the filevar option.

https://support.sas.com/techsup/technote/ts581.pdf

or Example 5 in 9.2 docs

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm

Your code as is doesn't include an import step, the macro loops isn't defined for us.

Your macro variables are probably not resolving properly, you can use options MPRINT and SYMBOLGEN to see them as they loop.

&& should be only one &

Another option would be to use Call execute instead of the loop.

Re: Import and Append all CSV files in folder - SAS EG

ballardw — Fri, 27 May 2016 20:09:49 GMT

Appending makes me believe all of the files are of the same structure (which is likely not be the case if you were planning on using Proc import).

Here's a recent thread on reading identical files. These were tab separated but the only change would be to change the delimiter.

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-import-and-merge-all-TSV-files-in-a-folder/m-p/273039#M18938

OP hasn't marked whether it was his solution but the last post is what I would suggest if thes are all the same, OR if you have common names with structures then to read each batch. The resulting data will be in order of the dataset names per your OS sort order which may not match your intent such as if month Names are used instead of month numbers in the file: XXXJAN will come after XXXAPR.