0

I just started using SAS 3 days ago and I need to merge ~50 csv files into 1 SAS dataset.

The 50 csv files have multiple variables with only 1 variable in common i.e. "region_id"

I've used SAS enterprise guide drag and drop functionalities to do this but it was too manual and took me half a day to upload and merge 47 csv files into 1 SAS file.

I was wondering whether anyone has a more intelligent way of doing this using base SAS?

Any advice and tips appreciated!

Thank you!

Example filenames: 2011Census_B01_AUST_short 2011Census_B02A_AUST_short 2011Census_B02B_AUST_short 2011Census_B03_AUST_short . . 2011Census_xx_AUST_short

I have more than 50 csv files to upload and merge.

The number and type of variables in the csv file varies in each csv file. However, all csv files have 1 common variable = "region_id"

Example variables: region_id, Tot_P_M, Tot_P_F, Tot_P_P, Age_0_4_yr_F etc...

JJFord3
  • 1,976
  • 1
  • 25
  • 40
user3513519
  • 11
  • 1
  • 2
  • 1
    At first you should import the data from csv files into SAS data sets using PROC IMPORT Statement. Then merge them using MERGE Statement. – andrey_sz Apr 09 '14 at 05:20
  • What are the filenames like? If they're similar we might be able to use SAS MACRO – mjsqu Apr 09 '14 at 09:09
  • Please provide some example filenames and file contents and we will be able to help you much better. @andrey_sz it is possible to import multiple CSVs into a single SAS dataset in one step. So while importing each into its own dataset first is a valid approach, it may not be necessary. – Robert Penridge Apr 09 '14 at 18:07
  • A similar question was asked some time back... http://stackoverflow.com/questions/19011325/append-multiple-csv-files-in-sas After importing the files using any of the answers in the previous post, you can write a macro to do the merging. Without more detail about the files, there isn't much else to suggest. – Keneni Apr 09 '14 at 20:49
  • Thanks guys for helping - i've tried PROC IMPORT to import a small number of data files (i.e. if uploading 5 files i repeated PROC IMPORT 5 times) Is there a way where i can import ~50 files without needing to repeat PROC IMPORT 50 times? And same goes for merging them.... – user3513519 Apr 10 '14 at 23:45

1 Answers1

0

First, we'll need an automated way to import. The below simple macro takes the location of the file and the name of the file as inputs, and outputs a dataset to the work directory. (I'd use the concatenate function in Excel to create the SAS code 50 times). Also, we are sorting it to make the merge easier later.

%macro importcsv(location=,filename=);
proc import datafile="&location./&filename..csv"
     out=&filename.
     dbms=csv
     replace;
     getnames=yes;
run;
proc sort data= &filename.; by region_id; run;
%mend;
%importcsv(location = C:/Desktop,filename = 2011Census_B01_AUST_short)
.
.
.

Then simply merge all of the data together again. I added ellipses simply because I didn't want to right out 50 times.

data merged;
merge dataseta datasetb datasetc ... datasetax; 
by region_id;
run;

Hope this helps.

JJFord3
  • 1,976
  • 1
  • 25
  • 40