BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Demographer
Pyrite | Level 9

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

You are missing a %mend Statement?

Demographer
Pyrite | Level 9

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?

Loko
Barite | Level 11

Hello,

 

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

Shmuel
Garnet | Level 18

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.

Kurt_Bremser
Super User

@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.

Kurt_Bremser
Super User

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.

Demographer
Pyrite | Level 9

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.

Kurt_Bremser
Super User

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.

Demographer
Pyrite | Level 9

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.

 

 

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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