Explicit reading of cells in Excel

[Sarath]

Hi SAS Experts,

1.I have a requirement to read an excel file in to SAS and generate a single SAS dataset

2.The said excel file has multiple worksheets.

3.The data is stored in a pre-determined cells whoose position is same in all worksheets

  example below

  worksheet1 - cell c10, d10,c20,d20, c30,d30 and so on.

  worksheet2 - cell c10, d10,c20,d20, c30,d30 and so on.

and so on.

4. My SAS Dataset must only contain the c10,d10 ---- cxxx, dxxx values from all worksheets one below the other.

5. I only know how to read a range of values like range of rows and columns in excel but not specific cells.

6. How do I make it happen?

Please convey your ideas?

Thanks,

[Kurt_Bremser]

Read the whole excel sheets into separate tables, and keep only the variables assigned to the "C" and "D" columns;

Add a line in the date step (which is repeated for all sheets):

if mod(_n_,10) = 0 then output;

Then concatenate the seperate tables into one with another data step.

[Sarath]

Thanks Kurtbemser,

However, the reading of entire rows, columns is not practicaly possible, as the excel file contains graphics, macros, excel formattings and lot more.. the data that needs to be read are residng in perticular cells and an explicit read is the requirement.

Regads

[Kurt_Bremser]

Either clean the unwanted data out through VBA, or save the sheet(s) as .csv and inspect the contents first before reading into SAS. .csv only contains data, no graphics, formulas, formats etc.

[RW9]

As an alternative to KurtBremser's suggestion, I would say learn VBA.  You can use VBA macros behind the scene to pull whatever data from sheets, combine it, then export a CSV file which you can then import into SAS.  Pretty simple -> Range("C10:D30").Cell.Copy, then paste to a new sheet then export.

[Ksharp]

Two options.

1) proc import ...... ;  range='Sheet1$:c10:d10' ;range='Sheet1$:c20:d20' ;  ......run;

2) libname x excel '........' ;  then pick them up like they are all sas datasets , my absolutely favorite .

Xia Keshan

[Sarath]

Hi Guys,

Thanks to all who took time to reply to this query, however none quite works for this issue in perticular.

Let me try explain further below

I need to read explicitly cells say , A101, A214, A617, A712, A1319, A1345, .... Axxx in to a data set by a field name say NAME. The cells can be anywhere for that matter.

What I was trying to find was whether there could ne a logical assiging as follows

Libname XXXXXX XXXXX  /** The path specification of the inout excel file */

Data MY_DATASET;

INFILE XXXXXX;

..... and then something liek this ...

NAME='sheet1|A101'; output;

NAME='sheet1|A214'; output;

NAME='sheet1|A617'; output;

NAME='sheet1|A712'; output;

.......

...

NAME='sheetxxxx|AXXXX';output;

run;

I cannot specify any range as the file contains images, functions, and other graphics and very many things, of whcih removing is not a practicable solution.

This motive is to make this SAS program automated and dynamic.

Hope this explains the issue better..

Regards

[RW9]

Well, where to start.  Excel is not a tool for <insert your task here>.  The reason being is that it is, unstructured, uncontrolled, hides things, etc.  Unfortunately, due to the flexibility does allow you to do a lot a things it was never built to do.  It sounds like, your trying to extract certain parts of that file for use in other software.  SAS works on the basis of datasets with variables and rows, its very rigid in its structure as with any proper data software (i.e. databases).  So, you have a clutered unstructured mess on the one hand, and a rigid highly structured setup on the other and you are attempting to squeeze the one into the other.  To do this you are going to have to perform some data-cleaning manipulation to make the transfer.  You can do this in a number of ways, these have been provided above, either import the excel file as flat tables and then find your data, or using VBA (which is embedded in Office Applications) process the file and export a CSV for importing to SAS.  There is no magic find my data in an Excel function.

If you know that the data you want is sheet1!A101, A214 etc. then you can do it either way:
VBA

Create new sheet

Copy cell sheet1!A101 to new sheet under A1

Copy cell sheet1!A214 to new sheet under A2

...

Export new sheet to file using CSV format.  (All the above are easily findable with a google search on VBA ...)

SAS

Import the whole sheet, or each sheet using your preferred method - libname proc import etc.

Find the data the you want and output to a new dataset.

Oh, just to add, if you want to make your code dynamic then create a nicely formatted file which can easily be imported into SAS.  Using Excel is fine, but keep it nice and simple.

[Ksharp]

You could do something like :

libname x excel 'c:\temp\x.xls' getnames=no;

data _null_;
 set sashelp.vmember(where=(libname='X'));
 call execute('data temp;
                 set x."'||memname||'"n;
                 retain tname "'||memname||'";
                 if _n_ in (10 20);run;
              proc append base=want data=temp force;run;');
run;

Xia Keshan