options compress=Yes; data salesdata_all; %let _EFIERR_ = 0; /* set the ERROR detection macro variable infile 'C:\Users\peter\salesdata_jun2020.csv' delimiter = ',' MISSOVER DSD TERMSTR=CRLF lrecl=32767 firstobs=2 ; informat ACCOUNT_NUMBER $32. ; informat FACILITY_CREATION_DATE anydtdtm40. ; format ACCOUNT_NUMBER $32. ; format FACILITY_CREATION_DATE datetime. ; input ACCOUNT_NUMBER $ FACILITY_CREATION_DATE ; if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ run;
Above is my SAS code for loading in the jun2020 sales data (csv format)
I have a lot of similar files that I would like to load in concatenate together.
They are:
etc...
As you can see they aren't always the same file name (with the month changed). I am struggling to write a macro to import all of this data and then concatenate together, rather than doing it manually. Any help would be appreciated.
Thanks
Corrected data step:
data want;
set files;
/* move global statements out of the loop, for better code readability */
length
ACCOUNT_NUMBER $32.
;
/* use LENGTH to define character variables, not FORMAT or INFORMAT */
informat
FACILITY_CREATION_DATE anydtdtm40.
;
format
FACILITY_CREATION_DATE datetime.
;
infile dummy filevar=fname end=done delimiter = ',' truncover dsd termstr=CRLF firstobs=2;
/* firstobs is an option of the INFILE statement */
do while (not done);
input
ACCOUNT_NUMBER
FACILITY_CREATION_DATE
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
output; /* or you'll only get the last observation of each file! */
end;
run;
Complete example, tested on SAS On Demand:
data _null_;
set sashelp.class;
file "~/class1.csv" dlm=",";
if _n_ = 1 then put "name,sex";
put name sex;
run;
data _null_;
set sashelp.class;
file "~/class2.csv" dlm=",";
if _n_ = 1 then put "name,sex";
put name sex;
run;
%let inpath=~;
data files;
length
dref $8
fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
do i = 1 to dnum(did);
fname = dread(did,i);
if scan(fname,-1,".") = "csv" and substr(fname,1,5) = "class"
then do;
fname = catx("/","&inpath.",fname);
output;
end;
end;
rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;
data want;
set files;
infile dummy filevar=fname end=done dlm="," firstobs=2;
do while (not done);
input name :$8. sex :$1.;
output;
end;
run;
Note the different separator (forward slash instead of backslash) used in the filename because of UNIX. Otherwise that same code will work in your SAS.
As long as all files share a common prefix and have the same suffix, you don't have to write a macro, using a wildcard in the infile-statement will automatically process all matching files. Unfortunately it seems that the files have an header-line. If you can't get those files without them, some more lines have to be written. Here's an example using sashelp.class:
%let data_dir = PATH_TO_ANY_DIR;
proc export data=sashelp.class(where=(Age <= 12)) dbms=csv file="&data_dir\class_one.csv" replace;
run;
proc export data=sashelp.class(where=(Age > 12)) dbms=csv file="&data_dir\class_another.csv" replace;
run;
data all;
length
Name $ 10
Sex $ 1
Age Height Weight 8
_filename fname $ 100
;
retain fname;
infile "&data_dir\class*.csv" dsd filename= _filename;
input @;
if _filename ^= fname or missing(_infile_) then do;
fname = _filename;
end;
else do;
input Name -- Weight;
output;
end;
run;
Naming files like that makes your work unnecessarily hard. For timestamps in filenames, always use a numeric YM(D) date order. Files will sort correctly, and you can use wildcards with much more confidence.
Even with multi-month files, 202008-09 will be much easier to handle than aug-sep2020.
If you can't use wildcards, read the filenames in a directory by using the DOPEN, DNUM and DREAD functions; store the filtered filenames in a dataset, which you can then use to dynamically read all files in a single data step by using the FILEVAR= option of the INFILE statement.
%let inpath=C:\Users\peter;
data files;
length
dref $8
fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
do i = 1 to dnum(did);
fname = dread(did,i);
if /* filter here */
then do;
fname = catx("\","&inpath.",fname);
output;
end;
end;
rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;
data want;
set files;
infile dummy filevar=fname end=done /* other options */;
do while (not done);
input .....;
output;
end;
run;
Thanks, I am trying to understand where I would put in my data load in code, is this right?
%let inpath=C:\Users\peter;
data files;
length
dref $8
fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
do i = 1 to dnum(did);
fname = dread(did,i);
if /* filter here */
then do;
fname = catx("\","&inpath.",fname);
output;
end;
end;
rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;
data want;
set files;
infile dummy filevar=fname end=done delimiter = ',' MISSOVER DSD TERMSTR=CRLF lrecl=32767 /* other options */;
do while (not done);
firstobs=2 ;
informat ACCOUNT_NUMBER $32. ;
informat FACILITY_CREATION_DATE anydtdtm40. ;
format ACCOUNT_NUMBER $32. ;
format FACILITY_CREATION_DATE datetime. ;
input
ACCOUNT_NUMBER $
FACILITY_CREATION_DATE
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
end;
run;
That looks pretty close. What errors did you get?
You need to put the FIRSTOBS=2 option into the INFILE statement. The way you have it now it is an assignment statement creating a dataset variable named FIRSTOBS that is always going to have the value 2.
You did not put any condition into the IF statement. That should be replaced by something like:
if scan(fname,-1,'.')='csv' then do;
so that only the CSV files are kept in the list of files. You could of course move the filtering to the next step, where you read in the list of files from the data step generated by the first step, to only read the subset of the files in the directory that match your condition.
Note you probably want to use the TRUNCOVER option instead of MISSOVER option. You almost NEVER want SAS to set the value to missing if there are not enough characters on the line to fully meet the informat used. You would rather that the truncated value be used instead.
You also do not need to set the _EFIERR_ macro variable. That is something that PROC IMPORT uses internally.
Corrected data step:
data want;
set files;
/* move global statements out of the loop, for better code readability */
length
ACCOUNT_NUMBER $32.
;
/* use LENGTH to define character variables, not FORMAT or INFORMAT */
informat
FACILITY_CREATION_DATE anydtdtm40.
;
format
FACILITY_CREATION_DATE datetime.
;
infile dummy filevar=fname end=done delimiter = ',' truncover dsd termstr=CRLF firstobs=2;
/* firstobs is an option of the INFILE statement */
do while (not done);
input
ACCOUNT_NUMBER
FACILITY_CREATION_DATE
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
output; /* or you'll only get the last observation of each file! */
end;
run;
Complete example, tested on SAS On Demand:
data _null_;
set sashelp.class;
file "~/class1.csv" dlm=",";
if _n_ = 1 then put "name,sex";
put name sex;
run;
data _null_;
set sashelp.class;
file "~/class2.csv" dlm=",";
if _n_ = 1 then put "name,sex";
put name sex;
run;
%let inpath=~;
data files;
length
dref $8
fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
do i = 1 to dnum(did);
fname = dread(did,i);
if scan(fname,-1,".") = "csv" and substr(fname,1,5) = "class"
then do;
fname = catx("/","&inpath.",fname);
output;
end;
end;
rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;
data want;
set files;
infile dummy filevar=fname end=done dlm="," firstobs=2;
do while (not done);
input name :$8. sex :$1.;
output;
end;
run;
Note the different separator (forward slash instead of backslash) used in the filename because of UNIX. Otherwise that same code will work in your SAS.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.