topic Reg Excel Appending in SAS Procedures

Reg Excel Appending

deleted_user — Wed, 01 Sep 2010 04:35:45 GMT

Hi

I am having daily sheets of 200 Excel sheets in a folder ,all are having same variaables .I want to create only one datset from all the 200 excel sheets

Re: Reg Excel Appending

Peter_C — Wed, 01 Sep 2010 05:54:15 GMT

is this a random collection of excel worksheets?
do these excel sheet have the same layout? (column order, data types, string lengths)?
do they have column headers in the first row, which are sufficient to identify that column of data and are these column headers unique enough to join similar data from all these sheets?

of course you could hope that running 200 proc import will provide coinsistent result data, but an obvious string column in one won't join up adequately with the same named column in other sheets when the others are empty or numeric.

good luck

Re: Reg Excel Appending

ArtC — Wed, 01 Sep 2010 05:57:41 GMT

A first pass would be to use PROC IMPORT for each sheet to create a work data set for that sheet followed by an APPEND (PROC DATASETS). It should be fairly easy to automate the process for all the sheets.