topic Save a file in external file in sas7bdat format in SAS Programming

Save a file in external file in sas7bdat format

San_rise — Fri, 23 Oct 2020 11:57:22 GMT

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

Re: Save a file in external file in sas7bdat format

Amir — Fri, 23 Oct 2020 12:24:38 GMT

Hi @San_rise ,

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

For each .xlsx file location:

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

Kind regards,

Amir.

Re: Save a file in external file in sas7bdat format

andreas_lds — Fri, 23 Oct 2020 12:29:18 GMT

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.

Re: Save a file in external file in sas7bdat format

San_rise — Fri, 23 Oct 2020 15:56:01 GMT

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.

Re: Save a file in external file in sas7bdat format

San_rise — Fri, 23 Oct 2020 15:59:54 GMT

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.

Re: Save a file in external file in sas7bdat format

Amir — Mon, 26 Oct 2020 10:54:42 GMT

Hi @San_rise ,

Try considering the following method:

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.
Assign a SAS library pointing to the location of your files.
For each file in the location, call the matching file-type macro to read the file (passing the parameter details).
Unassign the current library.
If there is another location then go back to step #2.

Kind regards,

Amir.

Re: Save a file in external file in sas7bdat format

San_rise — Thu, 12 Nov 2020 06:46:09 GMT

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