DATA Step, Macro, Functions and more

Problem to import several CSV files

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

Problem to import several CSV files

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?


Accepted Solutions
Solution
‎11-15-2016 07:53 AM
Super User
Posts: 7,762

Re: Problem to import several CSV files

[ Edited ]
Posted in reply to Demographer

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
PROC Star
Posts: 734

Re: Problem to import several CSV files

Posted in reply to Demographer

You are missing a %mend Statement?

Frequent Contributor
Posts: 111

Re: Problem to import several CSV files

[ Edited ]

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?

Super Contributor
Posts: 308

Re: Problem to import several CSV files

Posted in reply to Demographer

Hello,

 

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

Trusted Advisor
Posts: 1,554

Re: Problem to import several CSV files

[ Edited ]

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 @draycut noticed, you missed the %mend import; statement, to close the macro.

 

4) As @KurtBremser 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.

Super User
Posts: 7,762

Re: Problem to import several CSV files

Posted in reply to Demographer

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎11-15-2016 07:53 AM
Super User
Posts: 7,762

Re: Problem to import several CSV files

[ Edited ]
Posted in reply to Demographer

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 111

Re: Problem to import several CSV files

Posted in reply to KurtBremser

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.

Super User
Posts: 7,762

Re: Problem to import several CSV files

Posted in reply to Demographer

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 111

Re: Problem to import several CSV files

Posted in reply to KurtBremser

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.

 

 

Super User
Posts: 7,762

Re: Problem to import several CSV files

Posted in reply to Demographer

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 550 views
  • 4 likes
  • 5 in conversation