DATA Step, Macro, Functions and more

converting multiple xlsx file to sas data sets

Reply
Occasional Contributor
Posts: 5

converting multiple xlsx file to sas data sets

Hi All,

 

How to convert all 10 xlsx files to SAS data sets at a time. 

 

 

Regards,

Sony

 

Valued Guide
Posts: 2,175

Re: converting multiple xlsx file to sas data sets

do these .xlsx files have just one worksheet or more?

Are you running SAS on windows or unix or university edition?

 

 

 

Occasional Contributor
Posts: 5

Re: converting multiple xlsx file to sas data sets

Thank you for your responce.

 

excel contains one work sheet.

 

windows sas.

Occasional Contributor
Posts: 5

Re: converting multiple xlsx file to sas data sets

Hi Any updates on this.

Super User
Super User
Posts: 7,401

Re: converting multiple xlsx file to sas data sets

Your question is quite vague, so some overall possibilities:

1) As always, get your data in a proper data transfer format in the first place, CSV, XML, etc.  Far easier to work with, and more robust/secure.

2) Proc import each file, note that proc import is a guessing procedure so you may not end up with the structure you want.

You could of course macrotise the proc import to reduce coding somewhat. 

 

As with any question like this, its not so much about the technology used (although I would really not advise Excel), but the process.  Do you have an agreement with the supplier as to what the strcuture of each file will be, can they supply in a proper format.  Once you have that documentation the actual programming is very simple, basically just adding some syntax to the spec provided in the documentation.

New Contributor
Posts: 4

Re: converting multiple xlsx file to sas data sets

If the files can all be handled with the same code in the import then macro is definitely the way to go, just change the filename.xlsx in a macro variable with each iteration of the procedure.

 

However, another question is what are these going to be used for?  Are they all going to be merged into one SAS file (e.g., same observatrions, different variables)?  Are they goning to be concatenated (e.g., same variables, different observartions)?  If the latter, you can probably stick a proc append into the macro and not go through a separate datastep after the macro executes.

Occasional Contributor
Posts: 5

Re: converting multiple xlsx file to sas data sets

Hi,

 

Thank you so much for your responce.

 

These xlsx files are converting in to SAS data set and later on i am comparing with base and compare file.

 

These are basically used for comparision.

 

 

 

Super User
Posts: 3,108

Re: converting multiple xlsx file to sas data sets

[ Edited ]

Importing multiple sheets from one Excel workbook can be most efficiently handled by using the EXCEL LIBNAME engine and copying using PROC DATASETS. By default each sheet name becomes the SAS dataset name, so if possible change the sheet names to what you want as SAS dataset names first.

 

Edit: I now notice that your workbooks contain only one worksheet. In that case I suggest PROC IMPORT would be better as already suggested.

Occasional Contributor
Posts: 5

Re: converting multiple xlsx file to sas data sets

Hi,

 

Thank you so much for your responce.

 

I would like to import  all xlsx/csv into SAS data sets, what ever present in the folder, So i need the autamated code and the below code i am using and it is converting only one file.if you know any thing kindly provide me the CODE.

 

 

PROC IMPORT
DATAFILE="/ctshared/clinpharm/amg176/onc/20150161/cdm/sas/edc/data/ssd/adverse.xls"
OUT=WORK.test
REPLACE
DBMS=XLS;
OPTIONS VALIDVARNAME=V7;
/* SHEET="manufacturing_lot_data_report";*/
GETNAMES=YES;
RUN;

 

Regards,

Sony.

Super User
Super User
Posts: 7,401

Re: converting multiple xlsx file to sas data sets

Right, to answer your first question is two part.  Firstly you need to read in a directory listing from your OS.  This can be done in various ways, here is a Windows version using piping.  The second part is then to macro your code to be called for each file:

/* Macro imports file */
%macro Read (fname=);
  proc import datafile="c:\temp\&fname."
              out=work.test
              replace
              dbms=xls;
    options validvarname=v7;
    getnames=yes;
  run;
%mend Read;

/* Get list of files in given directory */
filename tmp pipe 'dir "c:\temp\*.xls" /b';
data _null_;
  length fn $200;
  infile tmp;
  input fn $;
  /* This generates one call to the macro per file from the read in */
  call execute(cats('%Read (fname=',fn,');'));
run;

You will also notice consistent casing, indentation and such like, and using the code insertion ( {i} above where you post) to retain all this, makes code easier to read.  Now the above is a bit more advanced, so if any of that doesn't make sense, its probably a good idea to read up on all that in the manual an understand what you are doing.

 

And, here is the big one.  You say you are reading output Excel files back into SAS for some sort of comparison, correct?  If so then I will say this.  Anything you read from Excel via proc import will be guarenteed not to match your original data.  Proc import is a guessing procedure, it checks some rows of data and guesses what the data is.  This is one primary reason why I would never use proc import to read in data - I always specify incoming data.  Second to this is Excel itself having no structure or consistency - it is not a database or data transfer format.  Imagine if you will that you play the lottery, you choose the "pick numbers at random" and combine that with the extreme unlikliness of winning anyways, do you think you will win - well there is a tiny fraction of a chance.

I would say, assess what your process is, why are you doing this?  What I tend to do for my outputs is to store a "one step away" dataset, this is a dataset which matches exactly to the output requirements.  All columns an sorting is present, all columns have fixed naming and properties.  A QC can then separately create the same dataset and QC/compare that.  The output is generated by a bare bones proc report, with a manual look at the output file - which you would have to do in any case - after that.

 

Ask a Question
Discussion stats
  • 9 replies
  • 381 views
  • 1 like
  • 5 in conversation