Help using Base SAS procedures

import multiple excel files into sas: with less steps

Posts: 38

import multiple excel files into sas: with less steps


Hi all,

I have created a data set with attributes(number, file name, cusip,isinname,de). I have wrote all the file names(path) in a filename(attribute) so that I can use the above dataset and import all the files using filename(attribute),, but i am not getting how to use dataset and import all the files using filename(attribute).

it will very helpful if sombody help me,

thanks in advance

Super User
Posts: 10,552

Re: import multiple excel files into sas: with less steps

Describe exactly what you would do with One example file.

How would you read/import it?

What would you name the output dataset?

What library would it go to?

Do these files have the same layout?

And why is Excel in the title of the post when it doesn't appear that any of these are Excel files?

Posts: 38

Re: import multiple excel files into sas: with less steps

actually i have 30-40 files in one folder. in that files i have to select only some files .filenames will be same everymonth and i have to concatenate YYYYMMDD(like \adp.daily.backdated.alre_trade_options_extract_191.dat.yyyymmdd) at end of thateach file name and i have to update only date eveymonth

so i thought to create a dataset with selected filesnames in filename attribute. so that i can use this dataset and use do loop to import all the files all at once.

1)i dont know how to import it. I just want to know using this dataset there is any way i can import files and I dont want to save in any library

2)no they dont have same layout

3)some of the files are in csv snd some are not

Super User
Posts: 10,552

Re: import multiple excel files into sas: with less steps

The idea of a control data set is a good one. You will want that to be a SAS data set for best use. I would store only the parts of the input data files that do not change. Personally I would keep the file extension as a separate field to simplify adding text.

You could start a process with parameters of 1) which file numbers to process (since it sounds like they may not all be done at the same time), the text that gets appended to those file names.

All SAS datasets, and other objects, are stored in a library. If you are not specifying which one then the default is the temporary library named Work. The Work library gets deleted when SAS shuts down, so I thought you might want the data saved in a permanent library that will persist between sessions. Also SAS datasets are currently limited to 32 characters and may only contain letters, digits and the underscore, "_" character. So a library would be one way to represent data from one folder or project allowing a shorter name for reference in other code.

You will need to provide some specific way to import each of the source files AND a destination SAS data set name. The destination name could be in that control data set and the date information can be added on creation. Since different file layout, for instance CSV versus fixed column, have different import characteristics you need to have some idea how you will read each one. Note that using Proc import can result in variable types changing varying between text and numeric depending upon contents.

After you have decided how to import at least one of those files we can look at how to expand the process.

A generic approach with a control data set similar to what you have proposed would be:

1) specify which files (records in the control set) are to be processed. One likely approach is to set a parameter that could be a macro variable to be a list of values. Then a WHERE clause selects them.

2) Specify the variant data elements such as that date string you mention. (NOTE: if you search the forum you'll find a number of threads related to doing similar things based on the date of execution)

3) Process the records with information in the set which tells which approach or possibly options for a proc Import or a different SAS program file to call.

4) Execute the import or appropriate steps

5) verify success

Posts: 38

Re: import multiple excel  files into sas: with less steps

thank you ..!Smiley Happy

Ask a Question
Discussion stats
  • 4 replies
  • 2 in conversation