topic Re: Problem to import several CSV files in SAS Programming

Problem to import several CSV files

Demographer — Tue, 15 Nov 2016 10:01:58 GMT

Hello,

I have 30 CSV files into a folder (P:\mydirectory). I would like to import them at once into SAS. I listed them into a csv file(listfiles .csv), which has this format:

Name_list

FileA.csv

FileB.csv

etc.

So I tried this:


proc import datafile="P:\mydirectory\listfiles.csv" out=work.listcsv dbms=csv replace;
   getnames=yes;
run;


proc sql;
select name_list into :name separated by '*' from work.listcsv;
%let count2 = 30;
quit;

%macro import;
%do i = 1 %to 30;
%let j = %scan(&name,&i,*);
proc import out = &j datafile="P:\mydirectory\&j";
dbms=csv replace;
getnames = yes;
run;
%end;

%import;

And nothing happens. Can you please help me to find why?

Re: Problem to import several CSV files

PeterClemmensen — Tue, 15 Nov 2016 10:03:45 GMT

You are missing a %mend Statement?

Re: Problem to import several CSV files

Demographer — Tue, 15 Nov 2016 10:10:06 GMT

Right...

But now it says that error:

ERROR: Libref '**the name of the file****' exceeds 8 characters.

In my example, I change the name of the csv files, but they have more than 8 characters. Is this a mandatory condition?

Re: Problem to import several CSV files

Loko — Tue, 15 Nov 2016 10:20:14 GMT

Hello,

The Error refers to the name of the library which in SAS is mandatory to have maximum 8 characters.

Re: Problem to import several CSV files

Kurt_Bremser — Tue, 15 Nov 2016 10:29:21 GMT

@Demographer wrote:

Right...

But now it says that error:

ERROR: Libref '**the name of the file****' exceeds 8 characters.

In my example, I change the name of the csv files, but they have more than 8 characters. Is this a mandatory condition?

Valid SAS names for SAS objects (filerefs, librefs, variables, macros, datasets, catalogs, ...) can only consist of letters, digits and underlines. They must start with a letter or underline, and have a maximum length of 32; filerefs and librefs have a maximum length of 8.

If you want to make your dataset names the same as the filenames, you have to remove the .csv extension and the path and make sure that the remainder adheres to the above conventions.

You get your error because SAS tries to interpret the filename (before the dot) as a libname and make "csv" the dataset name.

Re: Problem to import several CSV files

Kurt_Bremser — Tue, 15 Nov 2016 10:39:19 GMT

You can do all in one step, by creating the import steps with call execute. Try this:

data work.listcsv;
infile 'P:\mydirectory\listfiles.csv' firstobs=2 truncover;
input name_list $100.;
name_short = scan(name_list,1,'.'); * remove the .csv;
call execute('proc import out=work.'!!name_short!!' datafile="P:\mydirectory\'!!trim(name_list)!!'" dbms=csv replace;');
call execute('getnames=yes; run;');
run;

Untested, for lack of input data. But you should get the picture.

Edit: added a missing "v" in the dynamically created proc import dbms=csv.

Re: Problem to import several CSV files

Shmuel — Tue, 15 Nov 2016 10:50:13 GMT

Few comments:

1) SAS list uses a blank as separator. (repalce the '*' into ' ')
change your code line:

select name_list into :name separated by ' ' from work.listcsv;

2) You are not using the count2 macro variable. You can delete this line.

Maybe you wanted to get from sql the count of names. In this case:

Take out the %LET COUNT2 out from the SQL step and assign its value as:

proc sql;

...

quit;

%let count2 = &sqlobs; /* sqlobs is a SQL system macro variable */

then you could use it inside your macro, as:

%do i = 1 %to &count2;  /* count2 used instead 30 */

3) As @PeterClemmensen noticed, you missed the %mend import; statement, to close the macro.

4) As @Kurt_Bremser noticed, you need remove the .csv from the output table names,

otherwise SAs will interpret the filea.csv as table csv in library filea, which probably does not exist.

Re: Problem to import several CSV files

Demographer — Tue, 15 Nov 2016 10:47:51 GMT

Many thanks. That works.

However, I accounter one more problem. Some of the variables contain both numeric and string values (such as 1 2 3 C T). Thus, SAS considered as missing all the string values.

Re: Problem to import several CSV files

Kurt_Bremser — Tue, 15 Nov 2016 11:14:08 GMT

Since the individual files are imported with proc import, add a suitable guessingrows statement in the "call execute"d code.

If all your csv files in that directory share the same structure, you could use a tailored data step in place of proc import to read them.

Re: Problem to import several CSV files

Demographer — Tue, 15 Nov 2016 11:22:22 GMT

I tried adding GUESSINGROWS=100;

And I have the followed error message:

ERROR 180-322: Statement is not valid or it is used out of proper order.

All my CSV are in the same directory and have the same structure, but I am not really sure what should I do to use a tairloed data step properly with csv.

Re: Problem to import several CSV files

Kurt_Bremser — Tue, 15 Nov 2016 11:46:06 GMT

You need to add it like this:

data work.listcsv;
infile 'P:\mydirectory\listfiles.csv' firstobs=2 truncover;
input name_list $100.;
name_short = scan(name_list,1,'.'); * remove the .csv;
call execute('proc import out=work.'!!name_short!!' datafile="P:\mydirectory\'!!trim(name_list)!!'" dbms=csv replace;');
call execute('getnames=yes; guessingrows=100; run;');
run;

You can extract the data step from the log. With DBMS=CSV, proc import creates a data step and runs that to read the data. The data step is written to the log and can be used as a blueprint for your own efforts.

Basically, a data step for csv files looks like this:

data outfile;
infile "infile" dlm=',' firstobs=2 truncover lrecl=number;

truncover means that the data step will not skip into the next line if not enough columns are present in any given line

firstobs tells the data step to start in the 2nd line

lrecl=number sets a size for the input buffer (necessary if lines are longer than 254 bytes)

attrib
  var1 length= format=
  var2
  ...
;

define attributes like length, format, informat for your variables

input
  var1
  var2
  ...
;

read variables

run;

end data step