topic Re: Help merging multiple CSV files to a dataset. in SAS Programming

Help merging multiple CSV files to a dataset.

UT_Risom — Mon, 24 Feb 2014 18:02:34 GMT

I'm attempting to merge 160 CSV files into one dataset using macros. All of the files have similar filenames (BTLT4-#####-#) and 167 column variables. I tried to use the syntax listed below to do this, but I'm given the following error:

WARNING: Argument 1 to function DNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range. NOTE: Mathematical operations could not be performed during %SY

Any help with this will be appreciated!

Here is the syntax.

options mprint;

%macro MultImp(dir=,out=);

%let rc=%sysfunc(filename(mydir,"&dir"));

%let did=%sysfunc(dopen(mydir));

%let lstname=;

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

%if &memcount > 0 %then %do;mer

%do i=1 %to &memcount;

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

%let file=&dir.&lstname;

PROC IMPORT DBMS=CSV OUT= _&i

DATAFILE= "&file" REPLACE ;

GETNAMES=YES;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

DBSASLABEL=NONE;

TEXTSIZE=100;

RUN;

proc append data=_&i base=&out; run;

proc delete data=_&i; run;

%end;

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

%end;

%mend MultImp;

%MultImp(dir=C:\Users\Owner\Desktop\SAS Project\ACL CSV files,out=File);

Re: Help merging multiple CSV files to a dataset.

data_null__ — Mon, 24 Feb 2014 18:32:29 GMT

Perhaps if your CSVs are all the same you can read them using a file reference with wildcard.

filename csv160 'path/BTLT4-*.csv';

Re: Help merging multiple CSV files to a dataset.

data_null__ — Mon, 24 Feb 2014 18:34:23 GMT

Do you need those double quotes?

%let rc=%sysfunc(filename(mydir,"&dir"));

Re: Help merging multiple CSV files to a dataset.

Astounding — Mon, 24 Feb 2014 18:44:53 GMT

Have you tried examining &DID to see whether you have the proper syntax for identifying the folder?

Right after creating it would be the right place to stick a %PUT statement. Probably a value of 0 indicates the folder was not properly identified, but the documentation on DOPEN would confirm that.

Re: Help merging multiple CSV files to a dataset.

UT_Risom — Tue, 25 Feb 2014 00:52:07 GMT

After including %put &did; into my syntax, I was indeed given a value of 0. What can I do to fix this?

- SMR

As a reminder, this is the error I receive for the syntax above.

WARNING: Argument 1 to function DNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out

of range.

NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The

result of the operations have been set to a missing value.

Re: Help merging multiple CSV files to a dataset.

Astounding — Tue, 25 Feb 2014 02:29:45 GMT

To figure that out, I would have to read the documentation on DOPEN, and fiddle with it till I found what DOPEN expects. I would submit that you should do that part. Instead of MYDIR, figure out what DOPEN expects in this statement:

%let did = %sysfunc(dopen(mydir));

%put DID is &did;

Once you find a value of &DID that is nonzero, the rest will be much easier.

You won't need a full-blown macro. Playing with just those two lines of code would be enough.

Re: Help merging multiple CSV files to a dataset.

Tom — Tue, 25 Feb 2014 04:29:10 GMT

PROC IMPORT is NOT what you want to use if you have 160 files all with same 167 variables.

You might use it once and recall the code it generates to help you construct the code.

data want ;

* Define the 167 variables - You can use real names I will use var1-var167 as an example ;

length var1 var2 ..... var167 ;

* Add some INFORMAT and FORMAT statement for variables that need them, i.e. DATEs and TIMEs ;

* You do NOT want to attach either INFORMATS or FORMATS to simple strings and numbers;

infile "&mydir/BTLT4-*.csv" dsd truncover lrecl=32000 ;

* Throw away the header lines;

input @;

if upcase(_infile_)=:'VAR1,' then delete;

* Read the data ;

input var1 --- var167 ;

run;

Re: Help merging multiple CSV files to a dataset.

Kurt_Bremser — Tue, 25 Feb 2014 08:12:38 GMT

Hi!

If you try to open every file (of an unknown number of files) by itself, you may run out of available file handles (limit depending on the operating system).

In cases like this, I prefer to concatenate all the files with the help of the OS so I only have to read one file in SAS.

(eg UNIX "cat xxxx* > xxxx.concat").

But then, I have the blessing of running SAS on a Unix instead of Windows.

Re: Help merging multiple CSV files to a dataset.

data_null__ — Tue, 25 Feb 2014 11:36:22 GMT

I think it would be better to use the INFILE statement option EOV= that is intended for the purpose of detecteing when a new file has been opened than to use rely the value of a data line. The OP might also like to know the name of each file provided by the FILENAME= option.

Re: Help merging multiple CSV files to a dataset.

RW9 — Tue, 25 Feb 2014 15:13:22 GMT

Seonding Kurts answer here. If they are all the same type of file then "copy *.csv all_csv.csv" in CMD is probably easiest.

Re: Help merging multiple CSV files to a dataset.

data_null__ — Tue, 25 Feb 2014 16:05:32 GMT

I don't think that is a good idea at all. SAS will not open all the files and once and if we do as you suggest we won't be able to tell when a new file is opened EOV= or know the name of the file FILENAME=.

Re: Help merging multiple CSV files to a dataset.

RW9 — Tue, 25 Feb 2014 16:20:16 GMT

Not sure I follow you. I am suggesting appending the text CSV files outside of SAS into one big file. Then in SAS importing that one file to create the final dataset. This as an alternative to importing into SAS many files, then appending them. If you need to do it several times, save the dos commands in a batch file. Then each time, copy over all your .CSV files. Run the batch file to create one big .CSV, and then run your SAS program to import that one complete file. The SAS code is a very simple proc import then.

Re: Help merging multiple CSV files to a dataset.

Reeza — Tue, 25 Feb 2014 16:32:02 GMT

What about the header lines in each CSV file?

Re: Help merging multiple CSV files to a dataset.

data_null__ — Tue, 25 Feb 2014 16:52:24 GMT

RW9 wrote:

Not sure I follow you. I am suggesting appending the text CSV files outside of SAS into one big file.

And I am suggesting that you NOT do that because SAS has better tools to deal with reading concatenate files. Look at my first reply where I suggest reading the files using a wildcard then there is a reply from Tom where he supplies a more complete example.

Re: Help merging multiple CSV files to a dataset.

UT_Risom — Tue, 25 Feb 2014 17:38:10 GMT

After replacing the PROC IMPORT statement to the syntax below I'm still getting the following error.

dir=C:\Users\Owner\Desktop\SAS Project\ACL CSV files\

did=0

WARNING: Argument 1 to function DNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.

NOTE: Mathematical operations could not be performed during %SYSF

BTW, thanks for your help thus far! As a beginner with SAS macro all help is appreciated.

- SMR

options mprint;

%macro MultImp(dir=,out=);

%let rc=%sysfunc(filename(mydir,&dir));

%let did=%sysfunc(dopen(mydir));

%put dir=&dir;

%put did=&did;

%let filrf=MYDIR;

%let lstname=;

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

%if &memcount > 0 %then %do;

%do i=1 %to &memcount;

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

%let file=&dir.&lstname;

data want;

length var1 var2 ..... var167 ;

infile "&mydir\BTLT4-*.csv" dsd truncover lrecl=32000;

input @;

if upcase(_infile_)=:'VAR1,' then delete;

input var1 --- var167 ;

run;

proc append data=_&i base=&out; run;

proc delete data=_&i; run;

%end;

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

%end;

%mend MultImp;

%MultImp(dir=C:\Users\Owner\Desktop\SAS Project\ACL CSV files\,out=File);

Re: Help merging multiple CSV files to a dataset.

Peter_C — Tue, 25 Feb 2014 17:46:04 GMT

proposed a simple alternative to your macro

data want( compress= yes);

length var1 var2 ..... var167 $20 filen filename $100;

infile "&mydir\BTLT4-*.csv" dsd truncover lrecl=32000 eov=eov filename= filen firstobs=2;

input @;

filename= filen ;

if eov then delete;

input var1 --- var167 ;

run;

of course I'm not so I have probably got it "not quite right", but this is the size of the solution (subject to proper definition of 167 lengths and/or informats)

Re: Help merging multiple CSV files to a dataset.

Tom — Tue, 25 Feb 2014 17:49:23 GMT

I would suggest ditching the complex macro code to get the list of filenames.

My preference in order would be

1) Just use a wildcard in the filename you reference in the INFILE statement so that SAS will find the files for you.

2) Use operating system command like ls or dir with a pipe to read the filenames.

data files; infile "dir ""&mydir\BTLT4-*.csv"" /b" pipe truncover; input filename $256. ; run;

3) If you really must use the functions like DOPEN then do it in an actual DATA step where you can put in some PUT statements or even use the SAS datastep debugger to see what is happening.

Re: Help merging multiple CSV files to a dataset.

UT_Risom — Tue, 25 Feb 2014 23:05:51 GMT

I was able to create a macros to compile the list of files in my directory, just to see if that was the issue.

/*compiled directory*/

%macro get_filenames(location);

filename pipedir pipe "dir ""%unquote(&location)"" /b";

data files;

infile pipedir truncover;

input filename $256.;

put filename=;

run;

filename pipedir clear;

%mend;

%get_filenames(C:\Users\Owner\Desktop\SAS Project\ACL CSV files)

Unfortunately I'm still running into the same issue with the previous syntax, even after adjusting the infile statement. Are there any examples of alternative syntax (with or without macros) you recommend looking at, or if your willing to show me how to arrange what I have in a way that might be more efficient?

- SMR

/*adjusted syntax*/

options mprint;

%macro MultImp(dir=,out=);

%let rc=%sysfunc(filename(mydir,&dir));

%let did=%sysfunc(dopen(mydir));

%put dir=&dir;

%put did=&did;

%let filrf=MYDIR;

%let lstname=;

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

%if &memcount > 0 %then %do;

%do i=1 %to &memcount;

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

%let file=&dir.&lstname;

data files;

infile "dir ""&mydir\BTLT4-*.csv"" /b" pipe truncover;

input filename $256.;

run;

proc append data=_&i base=&out; run;

proc delete data=_&i; run;

%end;

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

%end;

%mend MultImp;

%MultImp(dir=C:\Users\Owner\Desktop\SAS Project\ACL CSV files\,out=File);

Re: Help merging multiple CSV files to a dataset.

Reeza — Tue, 25 Feb 2014 23:12:11 GMT

data files;

infile "dir ""&mydir\BTLT4-*.csv"" /b" pipe truncover;

input filename $256.;

run;

proc append data=_&i base=&out; run;

proc delete data=_&i; run;

%end;

This part doesn't look correct.

Should it be data &out or _&i or something else. Using a Static name with the wild card doesn't make sense after you've inputted all the file names as well.

Use the solution posted by Data _null_ above.

Re: Help merging multiple CSV files to a dataset.

UT_Risom — Tue, 25 Feb 2014 23:31:11 GMT

I tried the solution posted by Data _null_ above. Still the same issue.

- SMR