Blogs

Blogs

Importing CSV files into SAS datasets

7
By Topics | Learn SAS

Sometimes, your first impulse may not be correct, like trading in your practical sedan for a hot 2-seater.  Other times, your first impulse is perfect, as in the examples below.

Suppose the automobile data you wish to analyze resides in a CSV file.  Naturally, your first impulse is to import the CSV file into a SAS dataset.  Perfect!

Suppose you have a frequent need to import CSV files into SAS datasets.  Naturally, your first impulse would be to write a macro.  Perfect again!  Your macro might look something like this:

%macro importcsv(file);
   proc import 
      datafile="%superq(file)" 
      out=%scan(&file,-2,.\) replace;
   run;
%mend importcsv;
 
%importcsv(C:\automobiles\corvettes.csv)

Notice that the %SCAN function returns the name of the CSV file, minus the extension, as the name of the SAS dataset.

But, what if the CSV file has a name that is valid in Windows but is not valid in SAS?

The FIXNAME macro can fix invalid names:

%macro fixname(badname); 
    %if %datatyp(%qsubstr(&badname,1,1))=NUMERIC 
         %then %let badname=_&badname;
    %let badname=
         %sysfunc(compress(
             %sysfunc(translate(&badname,_,%str( ))),,kn));
    %substr(&badname,1,%sysfunc(min(%length(&badname),32)))
%mend fixname;

All you need to do is change the IMPORTCSV macro to call the FIXNAME macro:

%macro importcsv(file);
   proc import 
      datafile="%superq(file)" 
      out=%fixname(%scan(&file,-2,.\)) replace;
   run;
%mend importcsv;
 
%importcsv(C:\automobiles\Cool Corvettes!.csv)

You’ve then solved the issue, and the SAS log reports:

NOTE: WORK.COOL_CORVETTES data set was successfully created.

Before you drive off into the sunset, suppose you have hundreds of CSV files you wish to import.  No problem!

The SAS Macro Language 2: Advanced Techniques course includes the FINDCSV macro, which calls the IMPORTCSV macro to import all CSV files in a selected folder and all subfolders of the selected folder.  You will also learn more about the IMPORTCSV macro and the FIXNAME macro.

Finally, in addition to CSV files, you will learn automated macro techniques to import Microsoft Excel and Access files, and much more.  Check out the course outline for more details. See you in class!

Tags learn sas SAS macro sas training
Share

About Author

Jim Simon

Principal Technical Training Consultant

Jim Simon is a principal instructor and course developer for SAS Education. Jim has a bachelor's degree from UCLA and a master's degree from California State University at Northridge. Prior to joining the SAS Irvine office in 1988, Jim was an instructor at Ventura College and a SAS programmer at The Medstat Group in Santa Barbara. Jim's areas of specialization include the DATA step, application development, web enablement, and the SAS macro language. A native of Southern California, Jim enjoys anything in the warm California sun. On weekends, Jim loves jumping in his Corvette, turning up the stereo, and cruising Pacific Coast Highway, top down, South to Laguna Beach or North to his old home town, Santa Barbara.

Related Posts

7 Comments

  1. Hase on

    Thank you for your nice post. I'd like to use your sas dataset icon into our slide. May I use the image into my slide?

  2. Shamsul Arifin on

    Hello:

    I am trying to get import all the Excel or CSV files into respective Dataset from a Folder where any type of data files (xls, csv, txt, dbf) are there.

    First, I can get a list of specific type of files from this folder and create a Dataset say MyDataSet with those required files say, all csv files.

    Now, I would like to import all the files listed without file extension from the Dataset created as MyDataSet using the following Procedure

    call execute('
    proc import
    datafile="&Orig_SAS_Library.'||strip(FileName)||'"
    out=DataLib.'||strip(scan(FileName,1,'.'))||'
    dbms=EXCEL2000 replace;
    getnames=yes;run;');

    FileName is in the MyDataSet created before running Call Execute();

    Is any idea for this issue?

    it is appreciated for your time and effort for lending your hand.

    Regards,
    Shamsul Arifin

  3. Milorad Stojanovic on

    I like these codes. Every day is an opportunity to learn something new and elegant.

Back to Top