BookmarkSubscribeRSS Feed
Sony
Calcite | Level 5

Hi All,

 

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

 

 

Regards,

Sony

 

9 REPLIES 9
Peter_C
Rhodochrosite | Level 12

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

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

 

 

 

Sony
Calcite | Level 5

Thank you for your responce.

 

excel contains one work sheet.

 

windows sas.

Sony
Calcite | Level 5

Hi Any updates on this.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DocSteve
Obsidian | Level 7

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.

Sony
Calcite | Level 5

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.

 

 

 

SASKiwi
PROC Star

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.

Sony
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3290 views
  • 1 like
  • 5 in conversation