Save a file in external file in sas7bdat format

[San_rise]

An excel file - 'ABC.xlsx' already exists in an external folder. Requirement is to save the same file in the same location but in sas7bdat format.

I dont want to use the PROC IMPORT process as it will be lengthy process.

Please guide how effectively this can be done in SAS for multiple files stored in different locations.

Thanks

[Amir]

Hi @San_rise ,

For your high-level requirements you could try the following high-level steps:

For each .xlsx file location:

1. Allocate a library pointing to the same location.
2. Read in a previously unread .xlsx file using your preferred method, creating an output SAS data set to the library allocated in step #1.
3. If there is another unread .xls file in the same location then go back to step #2.
4. Unassign the current library. 

Kind regards,

Amir.

[San_rise]

This was the first thought that I had but due to different file types from multiple system paths I need to assign multiple libraries and then read each individual files and move them to the created library. It is a time taking job.

[San_rise · Accepted solution]

Any %macro %mend that can involve this diversity and just punching in the different paths and filenames. Any suggestion if I want to proceed with that how the syntax need to be framed.

Example: Location 1 --> File1.xlsx, File2.csv, File3.xlsb

Location 2 --> File4.csv

Location 3 --> File5.xlsx

Any best way wherein I can automate this in terms of importing them in SAS.

[Amir]

Hi @San_rise ,

Try considering the following method:

1. Define one macro per file-type that has to be read. Each macro should have parameters for: the input file location; the input file name; the output SAS library name; the output SAS data set name.
2. Assign a SAS library pointing to the location of your files.
3. For each file in the location, call the matching file-type macro to read the file (passing the parameter details).
4. Unassign the current library. 
5. If there is another location then go back to step #2.

Kind regards,

Amir.

[San_rise]

Thank you Amir. This does the work I was looking for.

[andreas_lds]

Code dealing with multiple excel-files is hardly effective, caused by the nature of the excel-file-format. Convert all files to csv and - if they have the same structure - importing them is easy.