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

Dear experts,

 

Me, the newbe, has created a macro to read in some csv files. It works but I think the code can be smarter since now I do this manually for something like 30 files which are all located in the same folder. Here an example for reading in 5 files.

 

the code in SAS EG:

%macro imp(infile);

PROC IMPORT OUT=SAS_TMP.&infile

datafile = "\\DWCPMCP\Data\01_INPUTDOCS\&infile..csv"

DBMS=csv REPLACE;

GETNAMES=YES;

datarow=6;

run;

%mend imp;

%imp(IL_V450)

%imp(IL_V500)

%imp(IL_V100)

%imp(IL_V350)

%imp(IL_V600)

 

My is, can I make this code less manually intensive? There are more files coming in. Can I import the whole folder in one time instead of typing them out?

 

Please advice.

 

 


CSV files.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

And best of all the doc has exactly your example 

 

Example 1: Import All CSV Files That Exist within a Directory

 

This is an annotated example that explains each step of the code.  Sample use:

 

%drive(c:\temp,csv)

View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Its a good idea to do a search before posting a question.  There are literaly hundreds of posts on this topic:

https://communities.sas.com/t5/forums/searchpage/tab/message?q=import+multiple+csv+files

 

You don't need macro, you can do it in a datastep and use wildcards:

infile "<pathtocsvs>\*.csv";

All you have to remember is proper CSV files have headers, so you need to jump one row per file.

Reeza
Super User

I would recommend the methods here. You may need some modification to start at line 6. 

Note that proc import guesses types so as the number of files gets larger it's more likely that you'll get inconsistent data types, ie a VAR is char in one dataset and numeric in another. 

 

Using method above requires you explicitly specify it, once. 

Reeza
Super User

And best of all the doc has exactly your example 

 

Example 1: Import All CSV Files That Exist within a Directory

 

This is an annotated example that explains each step of the code.  Sample use:

 

%drive(c:\temp,csv)
rogerjdeangelis
Barite | Level 11
* create three csvs;

dm "dexport sashelp.cars'd:\csv\cars.csv' replace";
dm "dexport sashelp.class'd:\csv\class.csv' replace";
dm "dexport sashelp.classfit'd:\csv\classfit.csv' replace";

* create three sas datsets;

%symdel fyl; * just in case it exists;
data _null_;
  do csv="classfit","class","cars";
     fyl=catx(' ',"dm 'dimport",cats('"d:\csv\',csv,'.csv"'),csv,"replace';");
     call execute(fyl);
  end;
run;quit;

NOTE: WORK.CLASSFIT data set was successfully created.
NOTE: The data set WORK.CLASSFIT has 19 observations and 10 variables.

NOTE: WORK.CLASS data set was successfully created.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.

NOTE: WORK.CARS data set was successfully created.
NOTE: The data set WORK.CARS has 428 observations and 15 variables.




Uzi
Fluorite | Level 6 Uzi
Fluorite | Level 6
Hi,
I have a question:
What should be changed in the code to select specific files from the folder?
Reeza
Super User

@Uzi That depends on what 'specific files' means. 

If they have a naming convention or all the same extension those would be different approaches.

 


@Uzi wrote:
Hi,
I have a question:
What should be changed in the code to select specific files from the folder?

 

Uzi
Fluorite | Level 6 Uzi
Fluorite | Level 6
Hey,

sorry for the delay and the lack of detail.
explanation:
I split the run into several computers to shorten the running time.
The result is multiple .csv files in the local  of multiple computers.
I have to group them back into one work file.
I can determine the names according to my convenience.

The problem:
There are several runs in each of the folders and I have to choose each time a specific run.
This means that each time I have to see the file names before importing and then choose who to import and group according to the conditions I set.
Before that I used Excel to consolidate the files and I could use a "flag"\"pointer" for the run I wanted to consolidate.
Hope I've been able to explain myself properly.
Thank you in advance for your help!
Uzi
Fluorite | Level 6 Uzi
Fluorite | Level 6
Small correction:
The name of the files will apply the year, the quarter, and the group from which it came
Reeza
Super User

I'm not understanding your question. I would suggest starting your own thread and explaining in detail..

Patrick
Opal | Level 21

@Uzi

Please start a new post with your question.

 

It's sometimes better to tell us what you have and what you need - and only then tell us what you've already tried. You've got a lot of experienced people in this forum and it's sometimes worth to give the big picture as then you might get solution approaches you haven't even thought about.

Uzi
Fluorite | Level 6 Uzi
Fluorite | Level 6
Thank you very much Patrick I will.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 11 replies
  • 39419 views
  • 6 likes
  • 6 in conversation